Extracting Particular String in SQL

Question

I need to extract or just retain certain characters from a varying string using SQL in Oracle. I used the SUBSTR function but that does not work for all the cases.

Example: Values are like this:

B2B_BID-XSKU957109
B2B_BID-SKU340535
B2B-SKU342345
B2B_BID-SKU340020201

Results I want:

SKU957109
SKU340535
SKU342345
SKU340020201

So its just not the last 10 characters but I want only the part which has the string 'SKU' and the number following it. I have given all kind of examples I have. Thanks

How exactly do you define the part you want? The second and last seem to lack everything before the last dash. What about the first one? Or do you always want to only take the last ten characters? — Till Helge
– Till Helge, Commented Jul 2, 2014 at 22:54
Ok. I added another example. So its just not the last 10 characters but I want only the part which has SKU and the number following it. — Sharan
– Sharan, Commented Jul 2, 2014 at 23:02

Gordon Linoff · Accepted Answer · 2014-07-02 23:04:45Z

2

You can get what you want with substr(), instr(), and reverse():

select substr(val, 1-instr(reverse(val), '-'))
from (select 'B2B_BID-XSKU957109' as val from dual) t

The above seemed to answer a slightly different question about getting the stuff after the last -. To get the stuff with the last SKU:

select substr(val, instr(val, 'SKU'))
from (select 'B2B_BID-XSKU957109' as val from dual) t

Here is a little SQL Fiddle.

answered Jul 2, 2014 at 23:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sharan Over a year ago

Yes. I wanted the stuff with the last SKU. Works like a charm. Thanks a lot Gordon!

Alex Gidan Over a year ago

Good one, though I think the solution with regexp is more compact and less demanding in terms of performance (no nested select). It is worth giving a look at the query explanation tool...

Sharan Over a year ago

Oh yea! This is for few million records and its taking a little time. Will run the explain plan and I think I might use the regexp method and check the performance on that one too ! Thanks Alex

Alex Gidan · Accepted Answer · 2014-07-02 23:17:44Z

Fixed token

If the token you're trying to extract is fixed length and always at the same place, you can use the substr function with negative index:

select substr('123456',-1,6) from dual;

S
-
6

select substr('123456',-6,6) from dual;

SUBSTR
------
123456

In your case it seems the token has variable length, though.

Variable Length Token

If the string you're trying to extract is not fix, you can't do it with traditional SQL or with the simple substr function. You could use regexp_substr. In this latter case, except if you really need for some exotic reason to treat your string in Oracle, I'd suggest you to do that in the application instead (or to double check carefully the regexp hit on Oracle performances).

Proposed Solution

SELECT REGEXP_SUBSTR('B2B_BID-SKU340020201','SKU[0-9]+') FROM DUAL;

Collectives™ on Stack Overflow

Extracting Particular String in SQL

2 Answers 2

3 Comments

Fixed token

Variable Length Token

Proposed Solution

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Fixed token

Variable Length Token

Proposed Solution

Comments

Your Answer

Sign up or log in

Post as a guest

Related