CODEWITHSUNDEEP
sqlstringoracle-databasesearch
northpole
northpole

Reputation: 10346

SQL Searching for specific value within a string

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.

Upvotes: 1

Views: 2497

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52645

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

Upvotes: 1

Dan A.
Dan A.

Reputation: 2924

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.

Upvotes: 8

Related Questions