CODEWITHSUNDEEP
sql
susanthosh
susanthosh

Reputation: 450

extract characters using delimiters

I am having string called '200_CFL_2010'. I wish to get the characters between _'s. I want the output as 'CFL'. I have to achieve it through SQL.

Upvotes: 2

Views: 167

Answers (2)

Andrew Bezzub
Andrew Bezzub

Reputation: 16032

select substring(col, 0, LEN(col) - charindex('_', col)) as col
from
(
    select substring(col, charindex('_', col) + 1, LEN(col)) as col
    from
    (
        select '200_CFL_2010' as col
    ) as subq
) as subq

Upvotes: 1

Jens
Jens

Reputation: 25593

I think this may be dependent on your Database (MySQL, Oracle, whatever..) Do a search for "string functions" and your database name. For e.g MySQL, you can find it here: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html.

The function INSTR and SUBSTR are the ones you are looking for. Use then like

SELECT SUBSTR(field, INSTR(field, "_", 1), INSTR(field, "_", 2) - INSTR(field, "_", 1)) FROM ...

Note: INSTR does only have two parameters in MySQL... youd need to cascade SUBSTRs a little there.

Upvotes: 1

Related Questions