0

My requirement is as below

In the table there is a field say ID containing value as A_B_C. The input will be having value 'C'. I need to get 'B' from ID field and then query again as ID like '%B%'.

I am trying to use below approach but it is not working. Can you please help me with right approach?

I have written a subquery using substring to get 'B' from ID as below

Subquery: select SUBSTR(ID,17,15) from where ID like '%C%' - this returns B

Now I want to write another select statement as " select * from where ID like '%<result of subquery should be used here%' "

ESri
  • 11
  • 1
  • 6

2 Answers2

2

I think this is what you are after, no need for Regex. It's just a correlated subquery, with the only difference being the LIKE condition instead of the common equality condition:

SELECT b.* 
FROM tablename AS b 
WHERE EXISTS 
      ( SELECT * 
        FROM words AS w 
        WHERE b.name LIKE '%' || w.word || '%'
      ) ;
  • Thanks you for quick response I tried this , it works fine if B exists however if B is not available it is returning all the rows. Below is query I am trying select * from table where ID like ('%'||(select SUBSTR(ID,20,15) from table where id like '%LOC12345%' and SUBSTR(ID,17,15) like '%PRI%' and rownum=1)||'%') If subquery is returning a value it returns required result however if subquery is empty it returns all the rows of table – ESri May 21 '21 at 07:19
0

SQL Like with a subquery This might be what you are looking for.

--Format SELECT * FROM item WHERE item_name LIKE '%' || (SELECT equipment_type FROM equipment_type GROUP BY equipment_type) || '%'

Justin
  • 321
  • 1
  • 5
  • 19
  • Thanks you for quick response I tried this , it works fine if B exists however if B is not available it is returning all the rows. Below is query I am trying select * from table where ID like ('%'||(select SUBSTR(ID,20,15) from table where id like '%LOC12345%' and SUBSTR(ID,17,15) like '%PRI%' and rownum=1)||'%') If subquery is returning a value it returns required result however if subquery is empty it returns all the rows of table – ESri May 21 '21 at 07:19