January 3, 2005 at 4:38 pm
Yup, nice and slick.
And bcp is a great tool.
You must unlearn what You have learnt
June 13, 2008 at 1:20 pm
I used this procedure fGetToken today and it works great! 🙂
June 13, 2008 at 10:41 pm
Like RockMoose did... no cursor... no loop... no RBAR from UDF usage. Slightly different take though... for ,, I return a Null... for , , I return a blank.
Borrowing heavily on RockMoose's set up code...
DROP TABLE #SlowThinker
GO
CREATE TABLE #SlowThinker
(
id int PRIMARY KEY,
ExData varchar(100)
)
SET NOCOUNT ON
INSERT #SlowThinker (id, ExData) VALUES (1,'Ed,Tom,1/1/2000,,,, ,,,,,,')
INSERT #SlowThinker (id, ExData) VALUES (2,'Tom,Jones,,dog,,,,,,,,,')
INSERT #SlowThinker (id, ExData) VALUES (3,'Paul,,,,,,,,,,,red,')
INSERT #SlowThinker (id, ExData) VALUES (4,'Steve,,,,,,,,,,,,')
INSERT #SlowThinker (id, ExData) VALUES (5,'1,Ed,Tom,1/1/2000,,,,,,,,,, ')
INSERT #SlowThinker (id, ExData) VALUES (6,'1,Tom,Jones,,dog,,,,,,,,,')
INSERT #SlowThinker (id, ExData) VALUES (7,'1,Paul,,, ,,,,,,, ,red,')
INSERT #SlowThinker (id, ExData) VALUES (8,'1,Steve,,,,,,,,,,,,')
INSERT #SlowThinker (id, ExData) VALUES (9,'2,big,A,,,')
INSERT #SlowThinker (id, ExData) VALUES (10,'2,small,A,,,')
INSERT #SlowThinker (id, ExData) VALUES (11,'2,,,,,')
--===== Declare a variable to hold the delimiter
DECLARE @D CHAR(1)
SET @D = ','
--===== Do the split with a count
SELECT ID,
MAX(CASE WHEN ColNum = 1 THEN Val END) AS N1,
MAX(CASE WHEN ColNum = 2 THEN Val END) AS N2,
MAX(CASE WHEN ColNum = 3 THEN Val END) AS N3,
MAX(CASE WHEN ColNum = 4 THEN Val END) AS N4,
MAX(CASE WHEN ColNum = 5 THEN Val END) AS N5,
MAX(CASE WHEN ColNum = 6 THEN Val END) AS N6,
MAX(CASE WHEN ColNum = 7 THEN Val END) AS N7,
MAX(CASE WHEN ColNum = 8 THEN Val END) AS N8,
MAX(CASE WHEN ColNum = 9 THEN Val END) AS N9,
MAX(CASE WHEN ColNum = 10 THEN Val END) AS N10,
MAX(CASE WHEN ColNum = 11 THEN Val END) AS N11,
MAX(CASE WHEN ColNum = 12 THEN Val END) AS N12,
MAX(CASE WHEN ColNum = 13 THEN Val END) AS N13,
MAX(CASE WHEN ColNum = 14 THEN Val END) AS N14
FROM (
SELECT ID,
ColNum = t.N-LEN(REPLACE(LEFT(@D+h.ExData+@D,t.N), @D, '')),
Val = CASE
WHEN CHARINDEX(@D, h.ExData+@D, t.N)-T.N > 0
THEN SUBSTRING(@D+h.ExData, t.N+1, CHARINDEX(@D, h.ExData+@D, t.N)-t.N)
ELSE NULL
END
FROM dbo.Tally t
RIGHT OUTER JOIN --Necessary in case ExData is NULL
#SlowThinker h
ON SUBSTRING(@D+h.ExData, t.N, 1) = @D
AND t.N <= LEN(@D+h.ExData)
)d
GROUP BY ID
If you don't have a Tally table, yet, or don't know how it works to replace certain types of loops, please see the following...
http://qa.sqlservercentral.com/articles/TSQL/62867/
--Jeff Moden
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply