Search code examples
sqlsplitcross-apply

Using STRING_SPLIT to normalize data in temp tables?

I have a use case where I need to create a table of values containing a bin location in a warehouse and the coordinate of that bin. My issue is the data is given to me in the following format:

(27,1) S,S,S
(1,34) F,F,F

I have an entire column of this data so my approach was to use STRING_SPLIT to separate the coordinate from the name.

INSERT INTO ##TestTemp
SELECT
s.CustomerID,
s1.[value] AS [BinInfo],
[RN] = ROW_NUMBER() OVER (ORDER BY CustomerID)
FROM ##TempTable s
CROSS APPLY STRING_SPLIT(s.[BinGrid], ' ') s1

SELECT * INTO ##CoordTemp
FROM ##TestTemp WHERE ([RN]%2)=1
SELECT * FROM ##CoordTemp

SELECT * INTO ##NameTemp
FROM ##TestTemp WHERE ([RN]%2)=0
SELECT * FROM ##NameTemp

My desired output is:

BinCoord BinNumb
(27,1) S,S,S
(1,34) F,F,F

I have used STRING_SPLIT and a CROSS APPLY after which I separate by RN (even and odd) into 2 temp tables, which gets me close to the desired result (see below), however I am having trouble linking the bin number to its coordinate. Trying to reconnect the data is giving me issues. Any help would be greatly appreciated!

BinCoord RN
(27,1) 1
(1,34) 3
BinNumb RN
S,S,S 2
F,F,F 4
Solution

  • I think you're over-thinking things.

    Based on your sample data and a single split-point on the space, simply use Charindex:

     with sampledata as (select * from (values('(27,1) S,S,S'),('(1,34) F,F,F'))x(BinGrid))

select Left(BinGrid, p - 1) BinCoord, Stuff(BinGrid, 1, p, '') BinNumb
from sampledata d
cross apply(values(NullIf(CharIndex(' ', Bingrid), 0)))s(p);