GET EXACT STRING MATCH [message #628002] |
Tue, 18 November 2014 18:10 |
|
kumar.chevva
Messages: 1 Registered: November 2014 Location: usa
|
Junior Member |
|
|
Below is the input data , i need to get data only which has "STE"
Input:
101 N. WESTLAKE BLVD. STE. C
18300 VON KARMAN, STE. 310
18200 S WESTERN AVENUE
840 HERNDON AVE, STE 102
11000 BOLLINGER CANYON ROAD
Expected output:
101 N. WESTLAKE BLVD. STE. C
18300 VON KARMAN, STE. 310
840 HERNDON AVE, STE 102
|
|
|
|
|
|
|
Re: GET EXACT STRING MATCH [message #628040 is a reply to message #628039] |
Wed, 19 November 2014 08:23 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I somehow read past the words "expected output", d'oh.
I assume the rule boils down to - the letters STE without another letter immediately before or after.
I'm sure someone can come up with a regexp for that. The instr approach LF suggested won't do the job.
|
|
|
Re: GET EXACT STRING MATCH [message #628041 is a reply to message #628040] |
Wed, 19 November 2014 08:50 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Well, LF's suggestion could be modified. As STE seems to have its own functional meaning. Thus, in OP's test data, there is a space before STE word.
Ideally, I would suggest to keep the significantly different attributes in different columns and follow normalization.
|
|
|
Re: GET EXACT STRING MATCH [message #628047 is a reply to message #628041] |
Wed, 19 November 2014 09:10 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Lalit Kumar B wrote on Wed, 19 November 2014 14:50Thus, in OP's test data, there is a space before STE word.
Hence my (badly phrased) questions, you can't be sure it's always going to be preceded by a space, it may be preceded by a comma or a full stop for example. It's address data and STE will be an abbreviation for something but whoever's entering the data isn't being consistent in how they write it.
|
|
|
Re: GET EXACT STRING MATCH [message #628057 is a reply to message #628047] |
Wed, 19 November 2014 10:15 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Point taken. Only OP can tell us now.
But, it's a bad idea to keep all the attributes of an address field in a single column. Doesn't matter what an user input is, at least the user's inputs would be seggregated from other attributes. My whole point is about normalization.
|
|
|
Re: GET EXACT STRING MATCH [message #628060 is a reply to message #628057] |
Wed, 19 November 2014 10:55 |
gazzag
Messages: 1118 Registered: November 2010 Location: Bedwas, UK
|
Senior Member |
|
|
I believe that this question is one about regexp rather than data modelling. Sadly, my regexp is not up to the task. It will however require specifying what additional characters should be counted as word separators/white space as CM has said ie. spaces and punctuation marks.
|
|
|
Re: GET EXACT STRING MATCH [message #628063 is a reply to message #628060] |
Wed, 19 November 2014 11:49 |
|
Michel Cadot
Messages: 68624 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> with data as (
2 select '101 N. WESTLAKE BLVD. STE. C' val from dual
3 union all
4 select '18300 VON KARMAN, STE. 310' val from dual
5 union all
6 select '18200 S WESTERN AVENUE' val from dual
7 union all
8 select '840 HERNDON AVE, STE 102' val from dual
9 union all
10 select '11000 BOLLINGER CANYON ROAD' val from dual
11 )
12 select val, regexp_substr(val, '\WSTE\W') tag
13 from data
14 where regexp_like(val, '\WSTE\W')
15 /
VAL TAG
---------------------------- ----------------------------
101 N. WESTLAKE BLVD. STE. C STE.
18300 VON KARMAN, STE. 310 STE.
840 HERNDON AVE, STE 102 STE
3 rows selected.
\W matches a non-word character, something that is not a word character.
Unfortunately Oracle does not support \b word boundary operator.
|
|
|
|
|
|