CODEWITHSUNDEEP
oracle11goracle10g
user2555212
user2555212

Reputation: 171

Include like clause value in result list

I have multiple like clauses on same column in my query. Is is possible to include the like clause value in result set? For example

SELECT * FROM address ADDR WHERE 
addr.line LIKE '%ONE%' 
OR addr.line LIKE '%TWO%'
OR addr.line LIKE '%THREE%'
OR addr.line LIKE '%FOUR%';

The resultset contains all the addresses including ONE,TWO,THREE,FOUR. Now i would like to see the result set like

Address1ONE | ONE
Address2TWO | TWO
THREEaddr   | THREE

any clue? -R

Upvotes: 0

Views: 33

Answers (1)

a1ex07
a1ex07

Reputation: 37374

SELECT addr.*,
CASE WHEN addr.line LIKE '%ONE%'  THEN 'ONE'
  WHEN addr.line LIKE '%TWO%'  THEN 'TWO'
  WHEN addr.line LIKE '%THREE%'  THEN 'THREE'
  WHEN addr.line LIKE '%FOUR%'  THEN 'FOUR' 
  ELSE 'Unknown'  -- redundant since other values filtered out by WHERE...
 END AS address_type 
 FROM address ADDR WHERE 
addr.line LIKE '%ONE%' 
OR addr.line LIKE '%TWO%'
OR addr.line LIKE '%THREE%'
OR addr.line LIKE '%FOUR%';

Upvotes: 1

Related Questions