How do I extract first characters of a string with delimiters

ritchierich

New Member
How do I extract first characters of a string with delimeters. For example I tried this statement:

Select "order-no" as "COL1", substr("order-no", 1, instr("order-no", '-',1, 2) ) as "COL2" from PUB."table"
where "order-no" like '4250%'

and got the following result:

Code:
COL1                    COL2
4250
4250-002
4250-002A
4250-002A-AA            4250-002A-
4250-002A-AA-A          4250-002A-
4250-002A-AA-B          4250-002A-

What I want is:

Code:
COL1                    COL2
4250                    4250
4250-002                4250-002
4250-002A               4250-002A
4250-002A-AA            4250-002A
4250-002A-AA-A          4250-002A
4250-002A-AA-B          4250-002A

Essentially I want to save whatever is after the first dash but also keeping ones that have zero or one dash.
 
Top