OraFAQ Forum: SQL & PL/SQL » specific text from the string

Home » SQL & PL/SQL » SQL & PL/SQL » specific text from the string (oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

specific text from the string [message #689923]

Tue, 30 July 2024 08:40

suji6281
Messages: 151
Registered: September 2014

Senior Member

Hi All,

How to get the text between two characters, are there any function in SQL.
I have tried with INSTR, SUBSTR, REGEXP_SUBSTR but didn't get the expected result.

Example:

Input Text: 56/971/267/80

I want the text between 1st slash (/) and second slash (/), i.e output should be 971.

Please help me with the SQL.

Thank you.

Regards
Suji

Report message to a moderator

Re: specific text from the string [message #689924 is a reply to message #689923]

Tue, 30 July 2024 09:10

suji6281
Messages: 151
Registered: September 2014

Senior Member

I tried this one, I got the result as 200. Which is expected.

SELECT
  rtrim(lTRIM(REGEXP_SUBSTR('14/200/60/00', '/[^/]+/'),'/'),'/') "REGEXPR_SUBSTR"
  FROM DUAL;

but are there any other best function to extract specific digits.

Thank you.

Regards
Sui

Report message to a moderator

Re: specific text from the string [message #689925 is a reply to message #689924]

Tue, 30 July 2024 12:12

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select trim(both '/' from regexp_substr('56/971/267/80','/[0-9]+/')) from dual;
TRI
---
971

1 row selected.

SQL> select regexp_substr('56/971/267/80','([^/]+)',1,2) from dual;
REG
---
971

1 row selected.

SQL> select regexp_replace('56/971/267/80','^[^/]+/([^/]+)/.*$','\1') from dual;
REG
---
971

1 row selected.

Report message to a moderator

Re: specific text from the string [message #689926 is a reply to message #689925]

Tue, 30 July 2024 12:39

Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA

Senior Member

OP never said string will always have two (or more) slashes, so be careful with regexp_replace:

SQL> select regexp_replace('56/57','^[^/]+/([^/]+)/.*$','\1') from dual
  2  /

REGEX
-----
56/57

SQL> select regexp_replace('56','^[^/]+/([^/]+)/.*$','\1') from dual
  2  /

RE
--
56

SQL>

SY.

Report message to a moderator

Re: specific text from the string [message #689927 is a reply to message #689926]

Tue, 30 July 2024 12:51

Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA

Senior Member

And SUBSTR might be more efficient:

with strings as (
                 select '56/971/267/80' str from dual union all
                 select '56/971' str from dual union all
                 select '56' str from dual
               )
select  str,
        substr(
               str,
               instr(str,'/') + 1,
               instr(str,'/',1,2) - instr(str,'/') - 1
              ) sub_str
  from  strings
/

STR           SUB_STR
------------- ----------
56/971/267/80 971
56/971
56

SQL>

SY.

Report message to a moderator

Re: specific text from the string [message #689928 is a reply to message #689927]

Tue, 30 July 2024 13:20

Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA

Senior Member

Actually, there is a caveat - NULL doesn't tell us if string doesn't have at least two slashes or first and second slashes are next to each other:

with strings as (
                 select '56/971/267/80' str from dual union all
                 select '56/971' str from dual union all
                 select '56//971' str from dual union all
                 select '56' str from dual
               )
select  str,
        substr(
               str,
               instr(str,'/') + 1,
               instr(str,'/',1,2) - instr(str,'/') - 1
              ) sub_str
  from  strings
/

STR           SUB_STR
------------- ----------
56/971/267/80 971
56/971
56//971
56

SQL>

If we care about such distinction then we can use something like:

with strings as (
                 select '56/971/267/80' str from dual union all
                 select '56/971' str from dual union all
                 select '56//971' str from dual union all
                 select '56' str from dual
               )
select  str,
        substr(
               str,
               instr(str,'/') + 1,
               instr(str,'/',1,2) - instr(str,'/') - 1
              ) sub_str,
        case instr(str,'/',1,2)
          when 0 then 'Not Found'
          else 'Found'
        end flag
  from  strings
/

STR           SUB_STR    FLAG
------------- ---------- ---------
56/971/267/80 971        Found
56/971                   Not Found
56//971                  Found
56                       Not Found

SQL>

SY.

Report message to a moderator