How to extract a sub-string from a string?

Question

fnd YGY LOOKUP_TYPE = 'welcome' HELO HIASDH LOOKUP_TYPE = 'home' hello how are you?

Above is the string and i want output as

welcome
home

Are you wanting to get any text that is enclosed in single quote marks ? Or is the text and the string exactly what you need to search ? — BriteSponge, Apr 18 '17 at 12:48
Please post some more sample data and desired result to clarify your need. Also, what have you tried so far? — Aleksej, Apr 18 '17 at 12:55
So you are looking for strings like `LOOKUP_TYPE = '...'`; is that part fixed? that is, can you have things like `LOOKUP_TYPE='...'` or `LOOKUP_TYPE = '...'` with more tha one space around the equal sign? — Aleksej, Apr 18 '17 at 13:14
Should the result hold a value per record or can the values be presented as a delimited string? — David דודו Markovitz, Apr 18 '17 at 13:59

MT0 · Answer 1 · 2017-04-21T07:15:52.207

3

Query 1:

Note: single quotes need to be escaped in a text literal:

WITH test_data ( string ) AS (
  SELECT 'LOOKUP_TYPE = ''welcome'' LOOKUP_TYPE = ''home'''
  FROM DUAL
)
SELECT SUBSTR( string, quote1 + 1, quote2 - quote1 - 1 ) AS first_quoted_string,
       SUBSTR( string, quote3 + 1, quote4 - quote3 - 1 ) AS second_quoted_string,
FROM   (
  SELECT string,
         INSTR( string, '''', 1, 1 ) AS quote1,
         INSTR( string, '''', 1, 2 ) AS quote2,
         INSTR( string, '''', 1, 3 ) AS quote3,
         INSTR( string, '''', 1, 4 ) AS quote4
  FROM   test_data
)

Output:

FIRST_QUOTED_STRING SECOND_QUOTED_STRING
------------------- --------------------
welcome             home

Query 2:

Note: Alternative text literal quote demiliters q'[your string]' used so that single quotation marks do not need to be escaped:

WITH test_data ( string ) AS (
  SELECT q'[LOOKUP_TYPE = 'welcome' LOOKUP_TYPE = 'home']'
  FROM DUAL
),
quotes ( string, quote_start, quote_end, lvl ) AS (
  SELECT string,
         INSTR( string, '''', 1, 1 ),
         INSTR( string, '''', 1, 2 ),
         1
  FROM   test_data
  WHERE  INSTR( string, '''', 1, 2 ) > 0
UNION ALL
  SELECT string,
         INSTR( string, '''', 1, 2 * lvl + 1 ),
         INSTR( string, '''', 1, 2 * lvl + 2 ),
         lvl + 1
  FROM   quotes
  WHERE  INSTR( string, '''', 1, 2 * lvl + 2 ) > 0
)
SELECT SUBSTR( string, quote_start + 1, quote_end - quote_start - 1 ) AS quoted_string
FROM   quotes

Output:

QUOTED_STRING
-------------
welcome
home

Query 3 - Regular Expressions:

Note: :your_string bind variable used to get input (you could also use a text literal instead):

WITH test_data ( string ) AS (
  SELECT :your_string FROM DUAL
),
quotes ( string, quoted_string, lvl, max_lvl ) AS (
  SELECT string,
         REGEXP_SUBSTR( string, q'[LOOKUP_TYPE\s*=\s*("|''?)(.*?)\1]', 1, 1, NULL, 2 ),
         1,
         REGEXP_COUNT( string, q'[LOOKUP_TYPE\s*=\s*("|''?)(.*?)\1]' )
  FROM   test_data
  WHERE  REGEXP_COUNT( string, q'[LOOKUP_TYPE\s*=\s*("|''?)(.*?)\1]' ) > 0
UNION ALL
  SELECT string,
         REGEXP_SUBSTR( string, q'[LOOKUP_TYPE\s*=\s*("|''?)(.*?)\1]', 1, lvl + 1, NULL, 2 ),
         lvl + 1,
         max_lvl
  FROM   quotes
  WHERE  lvl < max_lvl
)
SELECT quoted_string
FROM   quotes

Output:

QUOTED_STRING
-------------
welcome
home

edited Apr 21 '17 at 07:15

answered Apr 18 '17 at 12:50

MT0

143,790
11
59
117

why did u change the string? please keep the string same as i posted and in my string there is not double quotes only single quotes are there – Himansh Agarwal Apr 18 '17 at 13:07
@HimanshAgarwal If you want [a single quote `'` in a string](http://stackoverflow.com/q/2875257/1509264) it needs to be escaped using a second single quote `''` - the string would only have a single quote each location on output. The text of the string is irrelevant to the effectiveness of the answer - if you want it with your text then just substitute that in (escaping the single quotes). – MT0 Apr 18 '17 at 13:12
dude this is my string FDGF YGY LOOKUP_TYPE = 'welcome' HELO HIASDH LOOKUP_TYPE = 'home' hello how are you? i cannot change anything in my string. – Himansh Agarwal Apr 18 '17 at 13:14
@HimanshAgarwal and this answer will find the values in the quotes regardless of what the value of the string is.... put your string in and you will get the same answers. – MT0 Apr 18 '17 at 13:16
This was the string that you used LOOKUP_TYPE = ''welcome'' LOOKUP_TYPE = ''home'' this is the string that i am using FDGF YGY LOOKUP_TYPE = 'welcome' HELO HIASDH LOOKUP_TYPE = 'home' hello how are you? and this is the error which i am getting ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action: Error at Line: 2 Column: 42 – Himansh Agarwal Apr 18 '17 at 13:18
@HimanshAgarwal [Escape your single quotes with a second single quote](http://stackoverflow.com/q/2875257/1509264) or pass the string in as a bind parameter. – MT0 Apr 18 '17 at 13:20
@MT0 - maybe you could use the alternative string delimiters of q'[here's my string]'. That way Himansh will be reassured even though yours is exactly the same in function. – BriteSponge Apr 18 '17 at 13:20
@HimanshAgarwal - can I suggest that you look at the basics of quoting string literals in Oracle. Particularly with regard to strings containing quotes. http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm – BriteSponge Apr 18 '17 at 13:22
@BriteSponge updated with the `q'[text literal]'` syntax and a bind variable example. – MT0 Apr 18 '17 at 13:29
Query 3 - Regular Expressions: is working now what i want that this solution should also work with this DSFSFSDFSDFSD LOOKUP_TYPE = "XXX_ASD" ASAD HELO HIASDH LOOKUP_TYPE = "XX_DSASD" – Himansh Agarwal Apr 19 '17 at 06:20
can you merge both the query? – Himansh Agarwal Apr 19 '17 at 10:26
@HimanshAgarwal What do you mean by merging both queries? The 3rd option will handle both single and double quotes if that is what you are asking? – MT0 Apr 19 '17 at 10:39
Query 3 - Regular Expressions: is working now what i want that this solution should also work with this DSFSFSDFSDFSD LOOKUP_TYPE = "XXX_ASD" ASAD HELO HIASDH LOOKUP_TYPE = ''XX_DSAS'' – Himansh Agarwal Apr 20 '17 at 14:26
@HimanshAgarwal The 3rd option will handle both single and double quotes if that is what you are asking? – MT0 Apr 20 '17 at 14:47
i have a string where there is some times single quotes 'welcome' and some times double quotes "welcome" and some time two single quotes like this ' 'welcome' ' – Himansh Agarwal Apr 20 '17 at 15:20
@MT0 did you get what am i asking? – Himansh Agarwal Apr 20 '17 at 17:06
@MT0 dude it is still searching only for 'welcome' and "welcome" but skipping ' 'welcome' ' – Himansh Agarwal Apr 21 '17 at 05:48
@MT0 did u get what am i asking? – Himansh Agarwal Apr 21 '17 at 08:22

David דודו Markovitz · Answer 2 · 2017-04-20T15:23:54.267

1

with    t (col) as (select q'[fnd YGY LOOKUP_TYPE = 'welcome' HELO HIASDH LOOKUP_TYPE = "home" hello LOOKUP_TYPE = ''man'' how are you?]' from dual)

select  rtrim(regexp_replace(col,q'[(.*?LOOKUP_TYPE\s*=\s*(''|'|")(.*?)\2|.+)]','\3,'),',') as lookup_types
from    t

+------------------+
|   LOOKUP_TYPES   |
+------------------+
| welcome,home,man |
+------------------+

edited Apr 20 '17 at 15:23

answered Apr 18 '17 at 15:57

David דודו Markovitz

42,900
6
64
88

Query 3 - Regular Expressions: is working now what i want that this solution should also work with this DSFSFSDFSDFSD LOOKUP_TYPE = "XXX_ASD" ASAD HELO HIASDH LOOKUP_TYPE = ''XX_DSAS'' – Himansh Agarwal Apr 20 '17 at 14:20
I think you wanted to comment another answer – David דודו Markovitz Apr 20 '17 at 14:27
can you do what i want? – Himansh Agarwal Apr 20 '17 at 14:29
first thing is i am getting string from querying something but here string is fixed second i am having the string where there is two single quotes like this ''welcome'' single double quote "welcome" and single single quote 'welcome' – Himansh Agarwal Apr 20 '17 at 14:41
This solution returns all values as a delimited string if you need the values in separated rows you have the solution given by @MTO – David דודו Markovitz Apr 20 '17 at 14:47
your query is providing solution for the string where there is single quote like this 'welcome' but i have the string where there is one time single quote 'welcome' two time single quote ''welcome'' one time double quote "welcome" – Himansh Agarwal Apr 20 '17 at 15:06
You want double quotes? Always? – David דודו Markovitz Apr 20 '17 at 15:08
some times single quotes and some times double quotes and some time two single quotes like this ' 'welcome' ' – Himansh Agarwal Apr 20 '17 at 15:11
O.K. give me a minute – David דודו Markovitz Apr 20 '17 at 15:11
Wait, there is a cleaner solution – David דודו Markovitz Apr 20 '17 at 15:22
fantastic can you just do one more thing here our string is fixed i want this as bind variable – Himansh Agarwal Apr 20 '17 at 15:23
Check out the cleaner solution. – David דודו Markovitz Apr 20 '17 at 15:25
For bind variable use `select rtrim(regexp_replace(:my_bind_variable... from dual` – David דודו Markovitz Apr 20 '17 at 15:28
dude your query is working fine but i am getting all the values in single row can you make it appear in separated rows – Himansh Agarwal Apr 21 '17 at 06:22

How to extract a sub-string from a string?

2 Answers2