CODEWITHSUNDEEP
sqloracle-database
Lorenzo D
Lorenzo D

Reputation: 13

Extract text before third - "Dash" in SQL

Can you please help to get this code for SQL?

I have column name INFO_01 which contain info like:
D10-52247-479-245 HALL SO

and I would like to extract only
D10-52247-479

I want the part of the text before the third "-" dash.

Upvotes: 1

Views: 4882

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332591

Using a combination of SUBSTR and INSTR will return what you want:

SELECT SUBSTR('D10-52247-479-245', 0, INSTR('D10-52247-479-245', '-', -1, 1)-1) AS output
  FROM DUAL

Result:

output
-------------
D10-52247-479

Use:

SELECT SUBSTR(t.column, 0, INSTR(t.column, '-', -1, 1)-1) AS output
  FROM YOUR_TABLE t

Reference:

Addendum

If using Oracle10g+, you can use regex via REGEXP_SUBSTR.

Upvotes: 0

Brad Christie
Brad Christie

Reputation: 101614

I'm assuming MySQL, let me know if I'm wrong here. But using SUBSTRING_INDEX you could do the following:

SELECT SUBSTRING_INDEX(column, '-', 3)

EDIT Appears to be oracle. Looks like we may have to resort to REGEXP_SUBSTR

SELECT REGEXP_SUBSTR(column, '^((?.*\-){2}[^\-]*)')

Can't test, so not sure what kind of result that will have...

Upvotes: 0

davisb
davisb

Reputation: 11

Here is a simple statement that should work:

SELECT SUBSTR(column, 1, INSTR(column,'-',1,3) ) FROM table;

Upvotes: 1

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

You'll need to get the position of the third dash (using instr) and then use substr to get the necessary part of the string.

with temp as (
    select 'D10-52247-479-245 HALL SO' test_string from dual)
select test_string,
       instr(test_string,1,3) third_dash,
       substr(test_string,1,instr(test_string,1,3)-1) result
  from temp
);

Upvotes: 1

Related Questions