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

Are there consistent delimiters between first, last and middle? What happens if a first or last name is hyphenated? — Isolated
– Isolated, Commented Feb 24, 2023 at 16:18
I haven't see any examples with hyphenated data. So, far this is the pattern I have seen. And in some case Middle Name is NULL, in such case I have to populate it as NULL — Bruce
– Bruce, Commented Feb 24, 2023 at 16:25
which Sybase RDBMS product (ASE? SQLAnywhere? IQ? Advantage?) and version? — markp-fuso
– markp-fuso, Commented Feb 24, 2023 at 21:36
please update the question to include an example where the middle name is NULL — markp-fuso
– markp-fuso, Commented Feb 27, 2023 at 17:05

markp-fuso · Accepted Answer · 2023-02-28 22:09:00Z

2

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

edited Feb 28, 2023 at 22:09

answered Feb 24, 2023 at 21:25

markp-fuso

38.6k5 gold badges24 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bruce Over a year ago

I am getting the below error for desc_3 column when the value is NULL. How can I overcome this error Invalid length parameter with value of -17 passed to the substring function.

markp-fuso Over a year ago

@Bruce easy solution is to throw in some case statements; answer updated to eliminate the substring()/negative-value error

Bruce Over a year ago

Thank you. I am also trying below approach to remove ``` / ``` and ``` - ``` from the original value and tried using str_replace(desc,'/','') . This will replace only /, but I wanted to replace both / , - . How can I achieve this ?

Collectives™ on Stack Overflow

Sybase regexp, substr, instr function to split the string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related