0

Hi I need to find a pattern which randomly occur in a string in any order

The values in a column may be like these

'k=34,n=WER-RTN,l=hy33,z=device'

'k=34,l=hy33,z=device,n=WER-RTN'

'n=WER-RTN,l=hy33,z=device,k=34'

I need to pick up the value against n= till ',' else if it occurs at the end till last. but n can occur any where in the string.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Anith Shatamraj
  • 25
  • 2
  • 7

1 Answers1

1

If you want to get the WER-RTN value, you could extract that value with a regular expression like this one:

WITH t AS (SELECT 'k=34,n=WER-RTN,l=hy33,z=device' text FROM dual
           UNION
           SELECT 'k=34,l=hy33,z=device,n=WER-RTN' text FROM dual
           UNION
           SELECT 'n=WER-RTN,l=hy33,z=device,k=34' text FROM dual)
SELECT REPLACE(REPLACE(REGEXP_SUBSTR(text,'(n=.+?,)|(n=.+?$)'),'n='),',')
FROM t

If you're using Oracle 11g or superior you could simplify it a bit:

REGEXP_SUBSTR (text, '(^|,)n=([^,]+)', 1, 1, '', 2)
pablomatico
  • 2,222
  • 20
  • 25
  • A somewhat simplified select clause would be `SELECT REGEXP_SUBSTR (text, '(^|,)n=([^,]+)', 1, 1, '', 2) FROM t`. – collapsar Nov 20 '15 at 11:56
  • @collapsar what does the last parameter (2) mean? – pablomatico Nov 20 '15 at 11:59
  • 1
    @pablomatico The last parameter is the number of the subexpression. `0`represents the complete match,`1` ... `9` refer to the match portion corresponding to the `n`th subexpression in parentheses ( equivalent to `\1`...`\9` in other regex engines ). – collapsar Nov 20 '15 at 12:06
  • @collapsar it's not working for me, i get ORA-00939: too many arguments for function. I'm using Oracle 10gR2. Is it a new feature in newer versions? – pablomatico Nov 20 '15 at 12:18
  • @pablomatico Yes, you are correct, the subexpression parameter has been introduced with 11g. – collapsar Nov 20 '15 at 12:21
  • Thanks @collapsar! I just tried it on 11g and it worked! I'll include that in my answer – pablomatico Nov 20 '15 at 12:26