TransWikia.com

shorten the string to the desired values

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

3 Answers

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?

  • lines #1 - 5: sample data
  • line #8: starting point of the SUBSTR function is one character after the first > sign
  • line #9: length (used as the 3rd parameter of the SUBSTR) 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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP