CODEWITHSUNDEEP
sqloracle-database
Sunitha
Sunitha

Reputation: 135

using Substr and instr in SQL

I am trying to extract the middle of the string from record. I want to get the only middle of the string.

select instr('WUK00000105376:WUKE03960761:WUKR0093868603',':') 
from dual;

I want to get only WUKE03960761 from this string.

I have tried by using sustring and instring to get the output but i am not getting.

Upvotes: 2

Views: 5276

Answers (2)

user330315
user330315

Reputation:

Another option is to use regexp_substring() to get the string between the two colons:

select regexp_substr('WUK00000105376:WUKE03960761:WUKR0093868603',':[A-Z0-9]+:') 
from dual;

This however would also return the colons, but you can also tell regexp_substr() to return a specific group from the regex:

select regexp_substr('WUK00000105376:WUKE03960761:WUKR0093868603',':([A-Z0-9]+):',1,1,'i',1) 
from dual;

But Tim's solution using substr and instr is most likely going to be a lot faster.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

SELECT SUBSTR(col,
              INSTR(col, ':') + 1,
              INSTR(col, ':', 1, 2) - INSTR(col, ':') - 1)
FROM dual

Upvotes: 3

Related Questions