Putting results into strings...
-
Hi peoples,
I am having a problem with SQL Server 2000 and can't seem to crack where I am going wrong. I have got a set of results which I am putting into a string based on the length of the string, then the alphanumeric order. I have written a test case that explains the problem. Have a look.
--********************************Start
DECLARE @WorkingString varchar(100)
SET @WorkingString = ''
CREATE TABLE #TempControls(
CtrlCode varchar(3)
)
INSERT INTO #TempControls
VALUES ('T1')
INSERT INTO #TempControls
VALUES ('T2')
INSERT INTO #TempControls
VALUES ('T4')
INSERT INTO #TempControls
VALUES ('T7')
INSERT INTO #TempControls
VALUES ('T12')
SELECT @WorkingString = @WorkingString + CtrlCode + ', '
FROM #TempControls
WHERE CtrlCode LIKE 'T%'
ORDER BY LEN(CtrlCode) ASC, CtrlCode ASC
DROP TABLE #TempControls
SELECT @WorkingString
--********************************End
Which returns (unexpectedly)
"T12,"
I would expect it to return:
"T1, T2, T4, T7, T12,"
Now the interesting part is that if I comment out the "LEN(CtrlCode) ASC," part then I get the following results:
"T1, T12, T2, T4, T7,"
Which is correct in that it is returning the right results, but as you can see, not in the desired order (hence I was using the LEN(CtrlCode), then simply the result by alphanumeric order).
I know someone will probably suggest I take off the "T" part and then just order it numerically but sometimes CtrlCode can be "OPX244" or "JMMMYD98". I don't particularly want to cycle through the string and figure out what is the first numeric character....
I notice the LEN function causes issues in other places... is this another to add to the list?
Thanks!
-
1. Don't do this: SET @WorkingString = ''
2. SELECT @WorkingString = ISNULL(@WorkingString + ', ', '') + CtrlCode
_____________
Code for TallyGenerator -
True, but I still get the same result...
-
Well no, not exactly the same result but the SQL I am running now looks like:
DECLARE @WorkingString varchar(100)
CREATE TABLE #TempControls(
CtrlCode varchar(3)
)
INSERT INTO #TempControls
VALUES ('T1')
INSERT INTO #TempControls
VALUES ('T2')
INSERT INTO #TempControls
VALUES ('T4')
INSERT INTO #TempControls
VALUES ('T7')
INSERT INTO #TempControls
VALUES ('T12')
SELECT @WorkingString = ISNULL(@WorkingString + ', ', '') + CtrlCode
FROM #TempControls
WHERE CtrlCode LIKE 'T%'
ORDER BY LEN(CtrlCode) ASC, CtrlCode ASC
DROP TABLE #TempControls
SELECT @WorkingString
Which results in:
T12
being returned, nothing else... I am looking for:
T1, T2, T4, T7, T12,
The interesting thing is that if you run this:
DECLARE @WorkingString varchar(100)
CREATE TABLE #TempControls(
CtrlCode varchar(3)
)
INSERT INTO #TempControls
VALUES ('T1')
INSERT INTO #TempControls
VALUES ('T2')
INSERT INTO #TempControls
VALUES ('T4')
INSERT INTO #TempControls
VALUES ('T7')
INSERT INTO #TempControls
VALUES ('T12')
SELECT @WorkingString = ISNULL(@WorkingString + ', ', '') + CtrlCode
FROM #TempControls
WHERE CtrlCode LIKE 'T%'
ORDER BY /*LEN(CtrlCode) ASC,*/ CtrlCode ASC
DROP TABLE #TempControls
SELECT @WorkingString
The result is almost correct (all the items are returned, but the order is incorrect). Strange huh?
-
DECLARE @WorkingString varchar(100)
CREATE TABLE #TempControls(
CtrlCode varchar(3)
)
INSERT INTO #TempControls
VALUES ('T1')
INSERT INTO #TempControls
VALUES ('T2')
INSERT INTO #TempControls
VALUES ('T4')
INSERT INTO #TempControls
VALUES ('T7')
INSERT INTO #TempControls
VALUES ('T12')
SELECT IDENTITY(int, 1,1) as ID, CtrlCode
INTO #T
FROM #TempControls
WHERE CtrlCode LIKE 'T%'
ORDER BY LEN(CtrlCode) ASC, CtrlCode ASC
SELECT @WorkingString = ISNULL(@WorkingString + ', ', '') + CtrlCode
FROM #T
ORDER BY ID
DROP TABLE #TempControls
SELECT @WorkingString
_____________
Code for TallyGenerator -
Yep, this is the solution I came up with as well, but can anyone tell me why the "ORDER BY LEN(CtrlCode)" in my original post does not work??? Would prefer to make my query as fast as possible (man, I am one hard to please perfectionist!)
-
...and it's not just LEN... Try these in your order by...
' '+CtrlCode
... or ...
REPLACE(CtrlCode,'T','')
Looks like a genuine Microsoft Bug... gee... that's never happened before... bet the boys in Redmond will fix this just a soon as they hear about it, huh?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
1st. Hash table will be created behind the scene by SQL server, even if you don't realise it. So. this solution will not slow anything down.
2nd. If you are such a perfectionist you
a) would follow normalisation rules. Code "T12" definetely contains 2 parts - type code "T" and integer order number. Having those parts stored separately as it's required by the rules you'd never have this problem;
b) would not allow to run such query. Let your developers to build whatever strings they prefer in front-end application from data sets they get from database. Building strings - is not what RDMS is about.
_____________
Code for TallyGenerator -
Why is LEN even considered? Here is a safer approach.
-- Prepare test data
DECLARE @Temp TABLE
(
CtrlCode VARCHAR(8)
)
INSERT @Temp
SELECT 'TT4' UNION ALL
SELECT 'T1' UNION ALL
SELECT 'T4' UNION ALL
SELECT 'WE341' UNION ALL
SELECT 'T2' UNION ALL
SELECT 'Loop34' UNION ALL
SELECT 'Loop4' UNION ALL
SELECT 'XYZ20' UNION ALL
SELECT 'Base99' UNION ALL
SELECT 'T100' UNION ALL
SELECT 'Base10' UNION ALL
SELECT 'XYZ19' UNION ALL
SELECT 'WE10' UNION ALL
SELECT 'T12' UNION ALL
SELECT 'BCD5' UNION ALL
SELECT 'T7'
-- Do the work
DECLARE @WorkingString VARCHAR(8000)
SELECT @WorkingString = ISNULL(@WorkingString + ', ', '') + z.CtrlCode
FROM (
SELECT TOP 100 PERCENT CtrlCode
FROM @Temp
ORDER BY LEFT(CtrlCode, PATINDEX('%[0-9]%', CtrlCode) - 1),
CAST(SUBSTRING(CtrlCode, PATINDEX('%[0-9]%', CtrlCode), LEN(CtrlCode)) AS INT)
) z
--WHERE z.CtrlCode LIKE 'T%'
SELECT @WorkingString 'Sorted list'
Output is
Sorted list
----------------------------------------------------------------------------------------------
Base10, Base99, BCD5, Loop4, Loop34, T1, T2, T4, T7, T12, T100, TT4, WE10, WE341, XYZ19, XYZ20
And un-commenting WHERE z.CtrlCode LIKE 'T%' gives
Sorted list
------------------------------
T1, T2, T4, T7, T12, T100, TT4
N 56°04'39.16"
E 12°55'05.25" -
If you must use your original logic, rewrite as
SELECT @WorkingString = isnull(@WorkingString + ', ', '') + CtrlCode
FROM (select top 100 percent ctrlcode from #tempcontrols ORDER BY LEN(CtrlCode), CtrlCode) z
WHERE CtrlCode LIKE 'T%'
ASC can be omitted since this is the default sort order.
But beware that your logic can't differentiate between
T100
TT4
T2
T100 should come before TT4 (T2, T100, TT4), but it doesn't. It is output as T2, TT4 and T100.
Use my previously posted algorithm (safer approach) for correct results.
N 56°04'39.16"
E 12°55'05.25" -
Try this one for fun!
SELECT @WorkingString = ISNULL(@WorkingString + ', ', '') + CtrlCode
FROM #TempControls
WHERE CtrlCode LIKE 'T%'
ORDER BY 1
N 56°04'39.16"
E 12°55'05.25" -
Thanks guys, have implemented Dr Larsson's solution, but I am still none the wiser as to why my original doesn't work! Must be a bug I say!
-
Just a word of warning on this approach from the 'SQL Server Query Optimization Team'...
http://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Here's an alternative approach (for fun
)...--data
CREATE TABLE #TempControls(CtrlCode varchar(10))
INSERT #TempControls
SELECT 'TT4' UNION ALL
SELECT 'T1' UNION ALL
SELECT 'T4' UNION ALL
SELECT 'WE341' UNION ALL
SELECT 'T2' UNION ALL
SELECT 'Loop34' UNION ALL
SELECT 'Loop4' UNION ALL
SELECT 'XYZ20' UNION ALL
SELECT 'Base99' UNION ALL
SELECT 'T100' UNION ALL
SELECT 'Base10' UNION ALL
SELECT 'XYZ19' UNION ALL
SELECT 'WE10' UNION ALL
SELECT 'T12' UNION ALL
SELECT 'BCD5' UNION ALL
SELECT 'T7'
--add a computed column for ordering and make it clustered
ALTER TABLE #TempControls ADD CtrlCodeForOrdering AS
STUFF(CtrlCode, PATINDEX('%[0-9]%', CtrlCode), 0, space(10 - len(CtrlCode)))
CONSTRAINT CtrlCodeForOrdering UNIQUE CLUSTERED
GO
--calculation
DECLARE @WorkingString VARCHAR(100)
SELECT @WorkingString = ISNULL(@WorkingString + ', ', '') + CtrlCode
FROM #TempControls
WHERE CtrlCode LIKE 'T%'
DROP TABLE #TempControls
SELECT @WorkingString 'Sorted List'
/*results (the same as with Peter's idea)
Sorted List
----------------------------------------------------------------------------------------------
Base10, Base99, BCD5, Loop4, Loop34, T1, T2, T4, T7, T12, T100, TT4, WE10, WE341, XYZ19, XYZ20
And un-commenting WHERE CtrlCode LIKE 'T%' gives
Sorted List
------------------------------
T1, T2, T4, T7, T12, T100, TT4
*/
There are limitations, but I'm not going to worry about those.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Although I agree that doing an ORDER BY in a view is a bit crazy, I'd have to disagree with the author of the blog because I tried the methods he said would fail in the view and I can't get them to fail...all the methods posted return in descending order... guess I'm doing something wrong
And, the show-plan does show the sort...
StmtText
------------------
select * from v
(1 row(s) affected)
StmtText
---------------------------------------------------
|--Sort(ORDER BY
[t1].[col1] DESC))|--Table Scan(OBJECT
[tempdb].[dbo].[t1]))(2 row(s) affected)
I guess the real warning should be "test everything"...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply