March 1, 2011 at 10:37 am
Hi All!!
I often get given big lists of codes (account codes, call numbers, system numbers) to use in queries day to day, mainly for running one off SQL statements to grab data for people throughout the business.. They normally give me a spreadsheet with one column dedicated to the particular call number or system number etc. which I then turn into a string and use in a query.
So to help I wrote (butchered) a CTE to do the work for me, the only problem is I'm getting an extra space added in to the string, which is pretty annoying.. Even more annoying is the fact that I can't find where it's happening..
Anyone care to offer their help?? Feel free to suggest simplifications as well..
CREATE PROCEDURE [dbo].[sp_ConvertColumnString] (@len INTEGER, @char VARCHAR(1), @codes VARCHAR(MAX)) AS
BEGIN
SET QUOTED_IDENTIFIER OFF
/*
DECLARE @codes VARCHAR(MAX)
DECLARE @char VARCHAR(MAX)
DECLARE @len INTEGER
SET @len = 8
SET @char = ','
SET @codes = "('118152',
'296281',
'298349',
'298673',
'314224',
'413101',
'776009',
'CL11482',
'CO000004',
'CO00003',
'CO00005')"
*/
;WITH conv_strings AS(
SELECT CAST(1 AS INT) AS id,CHARINDEX(@char,@codes) charloc
UNION ALL
SELECT (id + 1) % @len,CHARINDEX(@char,@codes,charloc+1)
FROM conv_strings
WHERE charloc>0
)
SELECT TOP 1 SUBSTRING(@codes,0, charloc + 1) FROM conv_strings WHERE id = 0
UNION ALL
SELECT SUBSTRING(@codes,st.charloc + 1,(ISNULL(sto.charloc, LEN(@codes)) - st.charloc + 1))
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY id) AS id, charloc FROM conv_strings WHERE id = 0) st
LEFT JOIN
(SELECT ROW_NUMBER() OVER (ORDER BY id) - 1 AS id, charloc FROM conv_strings WHERE id = 0) sto
ON st.id = sto.id
OPTION (MAXRECURSION 10000)
END
I use the following to run the query each time, which you should be able to use to run the query:
SET QUOTED_IDENTIFIER OFF
exec sp_ConvertColumnString 7, ',',"'ABC',
'DEF',
'GHI',
'JKL',
'MNO',
'PQR',
'STU',
'VWX',
'YZ'
"
Second question is that if I enter a 3 as the @len (number of values per line returned) for the above string I get an error..
Any help is much appreciated 🙂
Cheers,
Jim.
March 2, 2011 at 6:08 am
Hi Jim
I don't think the problem is with an extra space but rather with carriage returns and line feeds in the original string. If you clean these out first then the "extra space" doesn't appear in the output. The type of splitting you are attempting here is reminiscent of some code I previously wrote for a related problem. I have modified this to generate the results you might be looking for. You may need to adapt further to suit your requirements.
SET QUOTED_IDENTIFIER OFF;
DECLARE @List VARCHAR(8000), @SplitOn CHAR(1), @chunk INT;
SELECT @List =
"'ABC',
'DEF',
'GHI',
'JKL',
'MNO',
'PQR',
'STU',
'VWX',
'YZ'
";
SELECT @List = REPLACE(REPLACE(@List, CHAR(13), ''), CHAR(10), ''), @SplitOn = ',', @chunk = 7;
WITH cteTally (N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..syscolumns
)
,
cteHOLD (StringValue, N, ROW, Value, endpos) AS
(
SELECT @List, N, ROW, Value, N + COALESCE(LEN(Value), 0)
FROM
(
SELECT N, ROW_NUMBER() OVER (ORDER BY N),
SUBSTRING(@List + @SplitOn, N, CHARINDEX(@SplitOn, @List + @SplitOn, N) - N)
FROM cteTally
WHERE N < LEN(@List) + 2
AND SUBSTRING(@SplitOn + @List + @SplitOn, N, 1) = @SplitOn
) AS Z (N, ROW, Value)
WHERE ROW % @chunk = 0
)
,
cteHOLD2 (StringValue, ROW, Value, endpos, start)
AS
(
SELECT cteHOLD.StringValue, cteHOLD.ROW, cteHOLD.Value, cteHOLD.endpos,
CASE cteHOLD.ROW
WHEN @chunk THEN 1
ELSE prev.N + LEN(prev.Value) + 1
END
FROM cteHOLD
FULL OUTER JOIN cteHold AS prev
ON cteHOLD.ROW = prev.ROW + @chunk
)
,
cteHOLD3 (start, comp)
AS
(
SELECT start, COALESCE(SUBSTRING(stringvalue, start, endpos - start + 1), SUBSTRING(@list, start, 8000))
FROM cteHOLD2
)
SELECT ROW_NUMBER() OVER (ORDER BY start) AS ID, comp
FROM ctehold3
WHERE NOT comp = '';
March 2, 2011 at 6:52 am
Jim, here's an alternative method. Assuming your new codes are in column A then insert the following into B1 and B2:
="SELECT '" & A1 & "' AS NewCode UNION ALL"
="SELECT '" & A2 & "' UNION ALL"
This will result in the following:
296281SELECT '296281' AS NewCode UNION ALL
298349SELECT '298349' UNION ALL
298673SELECT '298673' UNION ALL
314224SELECT '314224' UNION ALL
413101SELECT '413101' UNION ALL
776009SELECT '776009' UNION ALL
CL11482SELECT 'CL11482' UNION ALL
CO000004SELECT 'CO000004' UNION ALL
CO00003SELECT 'CO00003' UNION ALL
CO00005SELECT 'CO00005' UNION ALL
All you do then is copy the whole of column B into a query window, remove the superfluous UNION ALL from the end, and run it as a derived table, streaming into a target table, like this:
SELECT *
INTO #Staging
FROM (your select statements) d
It's quick and simple and handles up to 10,000 rows or so - be warned it will barf with much more than that.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 7, 2011 at 3:41 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply