CODEWITHSUNDEEP
sqloracle-sqldeveloper
Muhammad Asim
Muhammad Asim

Reputation: 159

How to select specific string from column?

I have a column which contains some values like this

PRODUCT CODE
A099  - Mouse Corded Other                  
X001  - Pointing Devices Family - FP&A Only 
W049A - Video Dualcam Other                 
N029  - Joystick PC Other                   
Y089  - Video Other Other                   
P059  - Gaming Wheels - FP&A Only

I want to fetch string or code before -. Is this possible ?

So the result set would be something like this

A099
X001
W049A
N029
Y089
P059

Upvotes: 0

Views: 68

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Use REGEXP_SUBSTR:

SELECT REGEXP_SUBSTR(product_code, '^\S+')
FROM yourTable;

But actually we don't even need to resort to regex here, the base string functions will do just as well:

SELECT SUBSTR(product_code, 1, INSTR(product_code, ' ')-1)
FROM yourTable;

In general, if you can avoid invoking a regex engine and instead use a simpler option, this can be the way to go.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Use can use regexp_replace():

select regexp_replace(product_code, '^([^ ]+) ', '\1')

Or regexp_substr():

select regexp_substr(product_code, '^[^ ]+')

Upvotes: 1

Related Questions