| Return value between two characters [message #629017] |
Tue, 02 December 2014 04:53  |
 |
SteveShephard
Messages: 41
Registered: August 2012
|
Member |
|
|
Hi all,
I have the following string
%^EMP_NO¬10^PA
Is it possible to write a select statement to return the value between %^EMP_NO¬ and ^PA (e.g. return the value 10)?.
Thanks
Steve
|
|
|
|
|
|
| Re: Return value between two characters [message #629019 is a reply to message #629017] |
Tue, 02 December 2014 05:51  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Assuming that there are always digits between %^EMP_NO¬ and ^PA, then you could use DIGIT class :
SQL> WITH DATA AS(
2 SELECT '%^EMP_NO¬10^PA' str FROM dual
3 )
4 SELECT regexp_substr(str, '[[:digit:]]+') str
5 FROM DATA
6 /
ST
--
10
SQL>
Else, you could use REPLACE as CM already suggested:
SQL> WITH DATA AS(
2 SELECT '%^EMP_NO¬10^PA' str FROM dual
3 )
4 SELECT replace(replace(str, '%^EMP_NO¬', ''),'^PA','') str
5 FROM DATA
6 /
ST
--
10
SQL>
You could anyway use the old SUBSTR + INSTR approach. It might look lengthy, but it would be better than regular expressions in terms of performance as they are resource hungry and CPU intensive processes.
[Updated on: Tue, 02 December 2014 05:52] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
