Extract string before the first "-" it finds and the space prior to the "-"

[kernal]

I have a field (i.e. TEST 100-001 Summer 2019) and I want to extract 100 from it.

All help is appreciated!

 

[carp]

Try

SELECT SUBSTR(myfield,1,INSTR(myfield,'-')-1) AS prefix FROM my_table;

 

[Andrzejek]

To clarify, your field contains:[tt][blue]
TEST 100-001 Summer 2019[/blue][/tt]
and you want to get [tt][blue]100[/blue][/tt] out of it?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

Andy - I was not sure of the requirements myself. By "extract" do we mean "retrieve" or "remove". The previous query would cover the "retrieve" portion. The following should cover "remove":

SELECT SUBSTR(myfield,INSTR(myfield,'-')+1,50) AS post_hyphen FROM my_table;

This assumes myfield will not be more than 50 characters long.

 

[Andrzejek]

You don't have to worry about 50 characters limit:

SELECT SUBSTR('TEST 100-001 Summer 2019',INSTR('TEST 100-001 Summer 2019','-')+1) AS post_hyphen FROM dual

you get: [tt]001 Summer 2019[/tt]

I replaced myfield with 'TEST 100-001 Summer 2019'

And you are right - it would be nice to know what kernal has and what he wants to get out of it...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

Oh, sure, Andy - exploit the function's features!