Search code examples
sqlsybasesubstrregexp-substrsybase-ase15

Sybase regexp, substr, instr function to split the string

I have the below data in a table.

row_num desc
1 First Name/Last Name - Middle Name
2 FirstName/LastName - MiddleName
3 FirstName/LastName

I am looking for the desired output as below

row_num desc_1 desc_2 desc_3
1 First Name Last Name Middle Name
2 FirstName LastName MiddleName
3 FirstName LastName NULL

In sybase I am not able to use functions like SUBSTR(desc,INSTR(desc,' ',1,1) + 1) . Need some inputs on how to achieve the desired result set in Sybase

Solution

  • Assumptions:

    • this is Sybase ASE so the functions of interest will be substring() and charindex()
    • OP wants a set-based SQL solution (eg, we're not parsing @variables as part of a looping/cursor-based construct)
    • all desc columns are of the format firstname/lastname - middlename (so we can search for the static delimiters / and -)

    The general approach is to use charindex() to find the positions of our delimiters and then feed these positions to substring().

    Since ASE does not support CTEs we'll use a derived table to generate the delimiter start positions:

    select  src.row_num,
        substring([desc], 1, dt.pos1-1)                 as desc_1,
        substring([desc], dt.pos1+1,
                          case when dt.pos2=0
                               then 100 
                               else dt.pos2-dt.pos1-1
                          end)                          as desc_2,
        case when dt.pos2=0 
             then NULL 
             else substring([desc], dt.pos2+3, 100) 
        end                                             as desc_3
from    src
join    (select row_num,
                charindex("/",  [desc]) as pos1,
                charindex(" - ",[desc]) as pos2
        from    src) dt
on      src.row_num = dt.row_num
order by 1
go

    NOTES:

    • desc is a reserved word in ASE hence the need to bracket the name ([desc])
    • for the 2nd substring() replace 100 with a number that's at least as long as the desc column
    • we could eliminate the derived table (dt) by replacing each dt.posX with the associated charindex() call (obviously the substring() calls would become a bit unwieldly)

    This generates:

     row_num desc_1     desc_2      desc_3
 ------- ---------- ----------- ---------
       1 First Name Middle Name Last Name
       2 FirstName  MiddleName  LastName

    FWIW, SQL Function/Reference manuals for the various Sybase RDBMS products: