Home » SQL & PL/SQL » SQL & PL/SQL » How To Retrieve string unto first pipe symbol in query (Oracle11g)
How To Retrieve string unto first pipe symbol in query [message #675432] Fri, 29 March 2019 02:26 Go to next message
ssyr
Messages: 64
Registered: January 2017
Member
Hello All,

I need to retrieve string from given string such as my string I like this '123456789|1235465778|' I want data upto prior to first pipe symbol

O/P: 123456789

Could you please help us for this.


Thank you in advance.
Re: How To Retrieve string unto first pipe symbol in query [message #675434 is a reply to message #675432] Fri, 29 March 2019 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 66807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SUBSTR/INSTR or REGEXP_SUBSTR

[Updated on: Fri, 29 March 2019 02:40]

Report message to a moderator

Re: How To Retrieve string unto first pipe symbol in query [message #675471 is a reply to message #675434] Mon, 01 April 2019 11:58 Go to previous messageGo to next message
Bill B
Messages: 1927
Registered: December 2004
Senior Member
substr(string,1,instr(string,'|')-1)
Re: How To Retrieve string unto first pipe symbol in query [message #675474 is a reply to message #675471] Tue, 02 April 2019 00:45 Go to previous messageGo to next message
ssyr
Messages: 64
Registered: January 2017
Member
Thank you for your reply.
Re: How To Retrieve string unto first pipe symbol in query [message #675475 is a reply to message #675474] Tue, 02 April 2019 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 66807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select regexp_substr('123456789|1235465778|','^[^|]+') from dual;
REGEXP_SU
---------
123456789

1 row selected.
Re: How To Retrieve string unto first pipe symbol in query [message #675507 is a reply to message #675475] Tue, 02 April 2019 14:03 Go to previous message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If the string always looks like this, then any of these options would work.
SQL> with test (col) as
  2    (select '123456789|1235465778|' from dual)
  3  select substr(col, 1, instr(col, '|') - 1) res1,
  4         regexp_substr(col, '\d+') res2,
  5         regexp_substr(col, '\w+') res3
  6  from test;

RES1      RES2      RES3
--------- --------- ---------
123456789 123456789 123456789

SQL>

If it doesn't, then it depends.

[Updated on: Tue, 02 April 2019 14:03]

Report message to a moderator

Previous Topic: Compare and contrast TRUNCATE and DELETE for a table
Next Topic: Storing only DATE I timestamp data type column
Goto Forum:
  


Current Time: Tue Jan 28 11:03:15 CST 2020