TSQL Split string

1

Hi ,
I have an excel formula that split the string. May I ask your help guys to convert this in tsql. thank you.
Please see below sample and DDL.

excel--
=RIGHT(LEFT(E11,7),6)&LEFT(RIGHT(E11,LEN(E11)-10),LEN(RIGHT(E11,LEN(E11)-10))-2)

--item---------------------------------------result---------------------------
S400-1721-PRZRMCROBLK-U----300-17PRZRMCROBLK
S400-0441-iPH5C16BLU-U--------300-04iPH5C16BLU

[code]create table #s
(item nvarchar(35))
insert into #s(item)values('S400-1721-PRZRMCROBLK-U')
insert into #s(item)values('S400-0441-iPH5C16BLU-U')

I'm trying to combined the code but I'm getting an error after removing the last 2 char (-U)

select item, RIGHT(LEFT(item,7),6),
Substring(item, charindex('-',item, charindex('-',item)+1)+1 ,len(item))
from #s[/code]

2

If I just convert that to SQL it seems to give the result you want (apart from "400" / "300" which I assume is a typo)

SELECT	RIGHT(LEFT(item,7),6) + LEFT(RIGHT(item,LEN(item)-10),
	LEN(RIGHT(item,LEN(item)-10))-2)
from #s

                                         
-----------------
400-17PRZRMCROBLK
400-04iPH5C16BLU

(2 row(s) affected)

If you actually want to find the last "-" and adjust according to that, rather than finding the "second occurrence", as you have tried to do, you might find it easier to use REVERSE on the String and then find the first "-" in that reversed-string.

3

Thank you very much Kristen. :slight_smile:

4

Just a different way. Don't know if it'll be faster or slower (probably slower but allows the parts to be different length, especially that first part) and it will only work if you have at least the right 3 parts (in this case).

 SELECT  Original = s.Item
        ,Reformed = '300-'+LEFT(PARSENAME(ca.Item,3),2)+PARSENAME(ca.Item,2)
FROM #s s
CROSS APPLY (SELECT REPLACE(s.Item,'-','.')) ca (Item) 
;

If you actually want to parse the numeric part of the first part, that'll take just a bit more.

5

Thank you guys, how about this one.

[code]select item, SUBSTRING(item,2,6)+ '-' +SUBSTRING(item,11,LEN(item)-12) AS itemid from #s

item itemid
S400-1721-PRZRMCROBLK-U 400-17-PRZRMCROBLK
S400-0441-iPH5C16BLU-U 400-04-iPH5C16BLU[/code]

6

That has one more dash in it than your original post. You could either STUFF out the extra dash or do a replace. Keep in mind that it also requires the first and second segment to both be precisely 4 characters at all times. If there's a guarantee of that, the a direct formula as you have would be the best for performance but is there a "forever" guarantee on the structure?

7

I avoid PARSENAME because I worry that there might be some "edge condition" when using it ... I've started a new thread to explore the issues:

http://forums.sqlteam.com/t/is-parsename-safe-for-general-paring-and-splitting/5990

8

Thank you very much for this information and ideas. :slight_smile: