extarct characters using delimiters

Question

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. Any idea please?

Answer 1

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.

Answer 2

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