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
Assumptions:
Sybase ASE
so the functions of interest will be substring()
and charindex()
@variables
as part of a looping/cursor-based construct)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]
)substring()
replace 100
with a number that's at least as long as the desc
columndt
) 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: