Sybase regexp, substr, instr function to split the string

Question

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

Answer 1

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:

• ASE functions
• SQLAnywhere functions
• IQ functions
• Advantage - having problems at the moment locating online documentation