SQL Searching for specific value within a string

Question

I need to find all the tables in our Oracle database that have attributes that match a certain word. So for example I am using:

SELECT TABLE_NAME
     , COLUMN_NAME
  FROM USER_TAB_COLUMNS
 WHERE UPPER(COLUMN_NAME) LIKE '%ING%'

This returns:

TABLE1 ING
TABLE2 THIS_ING
TABLE3 ING_FIRST
TABLE5 TESTING

I only want to return the results from TABLE1, TABLE2 and TABLE3. I don't want a match when it is only part of a string like TESTING.

I can't seem to get exactly what I need.

Hah, you are one those peculiar species that place the comma to precede the item on the next line. — Blessed Geek
– Blessed Geek, Commented Apr 26, 2012 at 15:38
are you also one of these species? haha, I picked up that habit from a friend who is a 20 year DBA veteran. It just seems to line up better when I do that. Additionally, you can comment out the attribute easier, you don't also have to comment out the comma on the preceding line if you happening to be commenting out the last attribute. — northpole
– northpole, Commented Apr 26, 2012 at 15:39
You do, however, have the opposite problem - you have to comment out the comma on the next line if you're commenting out the first item. — zimdanen
– zimdanen, Commented Apr 26, 2012 at 16:17

Dan A. · Accepted Answer · 2012-04-26 16:18:11Z

8

You can use a regular expression to find what you want:

SELECT TABLE_NAME
     , COLUMN_NAME
  FROM USER_TAB_COLUMNS
 WHERE regexp_like (column_name, '(^|(_)+)ING((_)+|$)')

This will give you columns that have ING preceded by either start of line or 1 ore more underscores, and followed by the end of line or 1 ore more underscores.

answered Apr 26, 2012 at 16:18

Dan A.

2,92420 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

northpole Over a year ago

This is it!! Thanks for the help, this site never fails me.

Conrad Frix Over a year ago

+1 nice that didn't take long for someone who actually knew regex, although I'm curious why not just (^|(_)+)ING

Dan A. Over a year ago

@ConradFrix I don't know if I'd go as far as saying I "know" regex, but given enough time, effort and luck I can hack something together (started on it before your answer was posted).

Dan A. Over a year ago

@ConradFrix Your example also pulls "INGRID", which I don't think the OP would want to see.

Conrad Frix Over a year ago

@DanA. Ah I see. I'm not sure if that's required but that's fine to go the extra mile

|

Conrad Frix · Accepted Answer · 2012-04-26 16:38:24Z

1

You could use REGEXP_LIKE. I'm not terribly good at regex, someone better than me could figure out how to do either start of a word or _ in one expression

SELECT TABLE_NAME
 ,     COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE
REGEXP_LIKE(COLUMN_NAME,'[_]ING')
or
REGEXP_LIKE(COLUMN_NAME,'^ING')

DEMO

edited Apr 26, 2012 at 16:38

answered Apr 26, 2012 at 16:15

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Collectives™ on Stack Overflow

SQL Searching for specific value within a string

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related