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:

    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:

    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: