How to select specific string from column?

Question

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

Yes, REGEXP_SUBSTR.

PM 77-1
– PM 77-1

2020-03-27 14:49:10 +00:00
Commented Mar 27, 2020 at 14:49 — PM 77-1
– PM 77-1, Commented Mar 27, 2020 at 14:49

Gordon Linoff · Accepted Answer · 2020-03-27 14:51:22Z

1

Use can use regexp_replace():

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

Or regexp_substr():

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

answered Mar 27, 2020 at 14:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Muhammad Asim Over a year ago

REGEXP_SUBSTR(PRODUCT_CODE, '[^-]+')

Gordon Linoff Over a year ago

@MuhammadAsim . . . It depends on whether there are spaces and whether you want them in the final result.

Muhammad Asim Over a year ago

I have used TRIM() to remove spaces. it worked fine though.

Tim Biegeleisen · Accepted Answer · 2020-03-27 14:52:36Z

1

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.

answered Mar 27, 2020 at 14:52

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

How to select specific string from column?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related