June 4, 2010 at 8:38 am
HI all,
I'm using some ciode I saw in a post here to return numbers from a comma delimited string.
The code works fine although the complexity is a bit above my understanding I understand the general theory.
However if I try to examine the numbers returned using ISNUMERIC()= 0 I get the error Invalid length parameter passed to the SUBSTRING function
My Test code is below , Can anyone see where I'm going wrong.
/* Parse a comma seperated string using a join */
-- make some test data
CREATE TABLE #Test
(ID INT IDENTITY,
TheString VARCHAR(255))
INSERT INTO #Test (TheString) VALUES ('1,2,3,4,5')
INSERT INTO #Test (TheString) VALUES ('1,4,5,6')
INSERT INTO #Test (TheString) VALUES ('1,10')
-- what does it look like
SELECT * FROM #Test
-- make a numbers table, in this example we only need 255 values
-- you could probably use a udf instead
SELECT TOP 255
IDENTITY(INT,1,1) AS Number
INTO
#Number
FROM
master.dbo.SysColumns sc1 CROSS JOIN master.dbo.SysColumns sc2 -- 12 million +
-- ** THIS WORKS FINE
-- JOIN is where the work is done, read that before the SELECT
SELECT
a.ID,
n.Number,
a.TheString,
SUBSTRING(',' + a.TheString + ',', n.Number + 1, CHARINDEX(',', ',' + a.TheString + ',', n.Number + 1) - n.Number - 1) -- n.Number is posn of a comma
AS ParsedString
FROM
#Test a INNER JOIN #Number n
ON n.Number > 0 -- dont start before 1
AND SUBSTRING(',' + a.TheString + ',', n.Number, 1) = ',' -- clever bit returns index into TheString at every comma, wrapping TheString in ' helps
AND n.Number < LEN(',' + a.TheString + ',') -- dont run off the end of the string
-- THIS FAILS
-- JOIN is where the work is done, read that before the SELECT
SELECT * FROM
(
SELECT
a.ID,
n.Number,
a.TheString,
SUBSTRING(',' + a.TheString + ',', n.Number + 1, CHARINDEX(',', ',' + a.TheString + ',', n.Number + 1) - n.Number - 1) -- n.Number is posn of a comma
AS ParsedString
FROM
#Test a INNER JOIN #Number n
ON n.Number > 0 -- dont start before 1
AND SUBSTRING(',' + a.TheString + ',', n.Number, 1) = ',' -- clever bit returns index into TheString at every comma, wrapping TheString in ' helps
AND n.Number < LEN(',' + a.TheString + ',') -- dont run off the end of the string
) X WHERE ISNUMERIC(ParsedString) = 0
-- Tidy up
DROP TABLE #Number
DROP TABLE #Test
June 4, 2010 at 10:08 am
Very odd!
I tried it with:
WHERE ParsedString LIKE '%[0-9]%'
and it came up with the same error, but actually returned the first string (ID = 1)
I also tried it with cross apply and wrapped it up in a CTE, but had the same issue.
On another note, I believe you can Tidy up the query a little in regards to concatenating less delimiters and what not (not that it makes much difference to your problem though)
SELECT
a.ID,
N.Number,
a.TheString,
N.ITEM
FROM #Test AS A
CROSS APPLY
(
SELECT TA.Number, SUBSTRING(A.TheString, TA.Number, CHARINDEX(',', A.TheString + ',', TA.Number) - TA.Number) AS Item
FROM #Number AS TA
WHERE TA.Number < LEN(A.TheString) + 2
AND SUBSTRING(',' + A.TheString , TA.Number, 1) = ','
) AS N
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply