how to extract this string using oracle sql or regex..
input : 'run_id, src_key, cd_key, **ml_orig as cde_value**, date_key, desc'
output: 'run_id, src_key, cd_key, **cde_value**, date_key, desc'
I want to output cde_val and replace any word before it until the previous column separated by "," and want to keep the other string intact after that.
before cde_value until the previous column, seprated by ",".
Try:
WITH src AS (
SELECT 'run_id, src_key, cd_key, ml_orig as cde_value, date_key, desc'' output: ''run_id, src_key, cd_key, cde_value, date_key, desc' X
FROM dual
)
SELECT x, regexp_replace( x, '(.*,)[^,]+as cde_value(.*)', '\1replacment\2') xx
FROM src
Demo: http://www.sqlfiddle.com/#!4/9fa35/2
before| run_id, src_key, cd_key, ml_orig as cde_value, date_key, desc' output: 'run_id, src_key, cd_key, cde_value, date_key, desc |
after | run_id, src_key, cd_key,replacment, date_key, desc' output: 'run_id, src_key, cd_key, cde_value, date_key, desc |
You can use
regexp_replace(val,'(^|, )[a-zA-Z_]+ as ','\1')
Regex explanation:
Second argument of regexp_replace is the pattern to be matched. In this case it is
(^|, ) matches either the start of the string or a , followed by a space.[a-zA-Z_]+ matches one or more times, characters in the range a-z or A-Z or an _ (underscore)[[:space:]]as matches space followed by as literally. The third argument is the replacement character(s). Here it is \1 (meaning the first group) which means you only retain the start of string portion preceding the pattern or ,[[:space:]].
Note that [[:space:]] was used in the explanation for clarity. Using a literal space character or [[:space:]] will work in a pattern match.
In SQL context, you will be replacing all the column-name followed by a space followed by as with nothing, which i assume is what's needed.
this will work:
select run_id, src_key, cd_key, (select regexp_replace(ml_orig,'(.)*',
run_id||','||src_key||','|| cd_key)from tablename) as cde_value, date_key, desc
from table name;
sample input output:
SELECT * FROM D061_WORDS;
nikhil sugandh
punam sugandh
mohit sugandh
select regexp_replace(a,'(.)* {1}',','||'sugandh') from d061_WORDS;
,sugandhsugandh
,sugandhsugandh
,sugandhsugandh
Don't neglect to test with the target pattern in all possible positions in the string, including not there at all. Always expect the unexpected! This method uses 3 capture groups. The first one is the start of the line or all characters followed by a space that occur before the second group. The second group is one or more word characters (a-z, A-Z, 0-9, including the _ (underscore) character) followed by 'as' . The third capture group is the rest of the line.
This is replaced by the 1st and 3rd capture groups, effectively removing the word before and including 'as'. Starts at position 1, does it for all occurrences ignoring case.
SQL> with tbl(id, str) as (
select 1, 'run_id, src_key, cd_key, ml_orig as cde_value, date_key, desc' from dual union all
select 2, 'ml_orig as cde_value, run_id, src_key, cd_key, date_key, desc' from dual union all
select 3, 'run_id, src_key, cd_key, date_key, desc, ml_orig as cde_value' from dual union all
select 4, 'run_id, src_key, cd_key, date_key, desc, ml_orig as cde_value' from dual union all
select 5, 'ml_oria as run_id, ml_orib as src_key, ml_oric as desc, ml_orid as cde_value' from dual union all
select 6, NULL from dual union all
select 7, 'run_id,ml_orig as src_key, cd_key, date_key, desc, ml_orig as cde_value' from dual
)
select id, regexp_replace(str, '(^|.*?, *)(\w+ +as +)(.*)', '\1\3', 1, 0, 'i') fixed
from tbl;
ID FIXED
---------- --------------------------------------------------
1 run_id, src_key, cd_key, cde_value, date_key, desc
2 cde_value, run_id, src_key, cd_key, date_key, desc
3 run_id, src_key, cd_key, date_key, desc, cde_value
4 run_id, src_key, cd_key, date_key, desc, cde_value
5 run_id, src_key, desc, cde_value
6
7 run_id,src_key, cd_key, date_key, desc, cde_value
7 rows selected.
SQL>
