Stack Overflow Asked on December 27, 2021
I have a table with a "Link" attribute. It has the following meaning:
<a href="https://help.company.ru/operator/#uuid:serviceCall$14612">INC102</a>
<a href="https://help.company.ru/operator/#uuid:serviceCall$146122">INC1020</a>
<a href="https://help.company.ru/operator/#uuid:serviceCall$1461">INC10200</a>
I want to get the following result:
INC102
INC1020
INC10200
I need to leave the INC and the numbers after it without .
Tell me which command will help here? Since I understand that "Substr" will not work here.
I am use SQL Developer – Oracle
You could treat [<>]
as the word delimiter and take the second word:
with test (col) as
( select '<a href="https://help.company.ru/operator/#uuid:serviceCall$14612">INC102</a>' from dual union all
select '<a href="https://help.company.ru/operator/#uuid:serviceCall$146122">INC1020</a>' from dual union all
select '<a href="https://help.company.ru/operator/#uuid:serviceCall$1461">INC10200</a>' from dual
)
select regexp_substr(col,'[^<>]+', 1, 2)
from test;
REGEXP_SUBSTR(COL,'[^<>]+',1,2)
-------------------------------
INC102
INC1020
INC10200
Answered by William Robertson on December 27, 2021
Since I understand that "Substr" will not work here.
Says who?
SQL> with test (col) as
2 (select '<a href="https://help.company.ru/operator/#uuid:serviceCall$14612">INC102</a>' from dual union all
3 select '<a href="https://help.company.ru/operator/#uuid:serviceCall$146122">INC1020</a>' from dual union all
4 select '<a href="https://help.company.ru/operator/#uuid:serviceCall$1461">INC10200</a>' from dual
5 )
6 select
7 substr(col,
8 instr(col, '>') + 1,
9 instr(col, '<', instr(col, '>')) - instr(col, '>') - 1
10 ) result
11 from test;
RESULT
------------------------------------------------------------------------------------------------------------------------
INC102
INC1020
INC10200
SQL>
What does it do?
SUBSTR
function is one character after the first >
signSUBSTR
) is position of the first <
that follows the first >
minus position of the first >
And that's it ... why wouldn't it work?
Answered by Littlefoot on December 27, 2021
If you just want to extract "INC" with the following digits, use regexp_substr()
:
select regexp_substr(link, 'INC[0-9]+')
Here is a db<>fiddle.
Answered by Gordon Linoff on December 27, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP