Home » SQL & PL/SQL » SQL & PL/SQL » GET EXACT STRING MATCH
GET EXACT STRING MATCH [message #628002] Tue, 18 November 2014 18:10 Go to next message
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 #628003 is a reply to message #628002] Tue, 18 November 2014 18:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: GET EXACT STRING MATCH [message #628007 is a reply to message #628003] Wed, 19 November 2014 00:04 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One option would be
... where instr(your_column, 'STE') > 0
Re: GET EXACT STRING MATCH [message #628022 is a reply to message #628007] Wed, 19 November 2014 02:42 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you want to get rows where STE is part of a word?
18200 S WESTERN AVENUE
Or on it's own:
840 HERNDON AVE, STE 102
18300 VON KARMAN, STE. 310

Re: GET EXACT STRING MATCH [message #628039 is a reply to message #628022] Wed, 19 November 2014 08:01 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Doesn't seem like OP needs the first option, since he did not include "18200 S WESTERN AVENUE" in the expected output.
Re: GET EXACT STRING MATCH [message #628040 is a reply to message #628039] Wed, 19 November 2014 08:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Lalit Kumar B wrote on Wed, 19 November 2014 14:50
Thus, 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: GET EXACT STRING MATCH [message #632298 is a reply to message #628063] Wed, 28 January 2015 13:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl12c> CREATE TABLE data
  2  as
  3  select '101 N. WESTLAKE BLVD. STE. C' val from dual
  4  union all
  5  select '18300 VON KARMAN, STE. 310' val from dual
  6  union all
  7  select '18200 S WESTERN AVENUE' val from dual
  8  union all
  9  select '840 HERNDON AVE, STE 102' val from dual
 10  union all
 11  select '11000 BOLLINGER CANYON ROAD' val from dual
 12  /

Table created.

SCOTT@orcl12c> DESC data
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VAL                                                VARCHAR2(28)

SCOTT@orcl12c> SELECT * FROM data
  2  /

VAL
----------------------------
101 N. WESTLAKE BLVD. STE. C
18300 VON KARMAN, STE. 310
18200 S WESTERN AVENUE
840 HERNDON AVE, STE 102
11000 BOLLINGER CANYON ROAD

5 rows selected.

SCOTT@orcl12c> CREATE INDEX data_idx ON data (val) INDEXTYPE IS CTXSYS.CONTEXT
  2  /

Index created.

SCOTT@orcl12c> SELECT val FROM data WHERE CONTAINS (val, 'STE') > 0
  2  /

VAL
----------------------------
101 N. WESTLAKE BLVD. STE. C
18300 VON KARMAN, STE. 310
840 HERNDON AVE, STE 102

3 rows selected.

Re: GET EXACT STRING MATCH [message #632588 is a reply to message #632298] Tue, 03 February 2015 06:46 Go to previous messageGo to next message
saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai
Junior Member
you can use
val like '%SET%'
Re: GET EXACT STRING MATCH [message #632590 is a reply to message #632588] Tue, 03 February 2015 07:03 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, you can't.
The purpose is to get the word STE (and not the string SET).

Previous Topic: Values on NUM_ROWS of DBA_TABLE is appearing as null for 48 records
Next Topic: Wait class snapshot wise using dba_hist_system_event
Goto Forum:
  


Current Time: Thu Mar 28 06:43:38 CDT 2024