CODEWITHSUNDEEP
sqloracle-databaseurl-parsing
buka korek
buka korek

Reputation: 59

PLSQL extract singles value

I want extract single values from links:

    Column1                                            
    ---------------
   <a href=" https://link; m=date1>Link</a>  
   <a href=" https://link; m=date2>Link</a>

The resultant column should look like:

    Column1                                    Column2   
    ---------------
   <a href=" https://link; m=date1>Link</a>     date1 
   <a href=" https://link; m=date2>Link</a>     date2

Upvotes: 0

Views: 50

Answers (3)

Gary_W
Gary_W

Reputation: 10360

Assuming the exact sample data given with one target "m=<value wanted>" pattern per line, you can use this REGEXP_REPLACE() example:

with tbl(id, str) as (
  select 1, '<a href=" https://link; m=date1>Link</a>' from dual union all  
  select 2, '<a href=" https://link; m=date2>Link</a>' from dual union all
  select 3, '<a href=" https://link; m=>Link</a>' from dual union all
  select 4, NULL from dual
)
select id, regexp_replace(str, '.*m=(\w+).*', '\1') col1
from tbl;

Match "m=" anywhere in the line and save one or more word characters that follows into a remembered group. Return the first remembered group. Note if the pattern is not found the input string will be returned. You could make it return a NULL (in the case of line 3) by making the group match zero or more word characters: '.*m=(\w*).*'. Think carefully about what you want returned if a match is not found.

Upvotes: 0

tus124
tus124

Reputation: 16

Try the following code:

insert into B_TEST_TABLE values('<a href=" https://link; m=date1>Link</a>');
insert into B_TEST_TABLE values('<a href=" https://link; m=date2>Link</a>');

select COLUMN1, 
substr(COLUMN1, instr(COLUMN1, 'm=') +2 , instr(COLUMN1, '>') - (instr(COLUMN1, 'm=') + 2)) 
from B_TEST_TABLE;

Upvotes: 0

Popeye
Popeye

Reputation: 35920

Try regexp_substr as following:

SQL> SELECT
  2      REGEXP_SUBSTR('<a href=" https://link; m=date1>Link</a>', 'm=(.*?)>', 1, 1, NULL, 1)
  3  FROM
  4      DUAL;

REGEX
-----
date1

SQL>

Cheers!!

Upvotes: 0

Related Questions