February 12, 2009 at 6:34 am
Need to order by alphanumeric
Example:
Input: L1,L2,L3,L5,L7,L6,L8,L4
Output: L1,L2,L3,L4,L5,L6,L7,L8
February 12, 2009 at 8:26 am
Here's a solution from my recipe file. If you don't already have a tally table, the code to create one is at the bottom. The smiley face is actually just a close parentheses ")"
--------------------------------------------------------------------
DECLARE @input varchar(max)
DECLARE @output varchar(max)
DECLARE @workTable table (element varchar(max))
SET @input = 'L1,L2,L3,L5,L7,L6,L8,L4'
SET @input = ','+@input+','
-- use tally instead of XML because it parses quicker
insert into @worktable
select substring(@input,N+1,charindex(',',@input,N+1)-(N+1)) as N
from tally
where substring(@input,N,1) = ','
and N < len(@input)
-- xml is quicker at building the output string
select @output = stuff(( SELECT ',' + element
FROM @worktable
ORDER BY element
FOR XML PATH('')
) ,1,1,'')
select @input
select @output
---------- code to create a tally table follows
--========================================================
-- Create and populate a Tally table-- thanks to Jeff Moden
--==========================================================
-
- Conditionally drop
IF OBJECT_ID('dbo.Tally') IS NOT NULL DROP TABLE dbo.Tally
-- Create and populate the Tally table on the fly
SELECT TOP 1000000 -- limit to one million rows
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 -- cross join to itself
-- Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
-- Let the public use it
GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 12, 2009 at 12:31 pm
[font="Verdana"]That Tally table (or Numbers table, same concept) is so useful! You can do this without the XML, by the way, but I think the XML makes for a better solution.[/font]
February 15, 2009 at 10:15 am
I just looked at this post again and realized I need to revise my recipe. There is really no need for a table variable here, a cte is good enough. I'm just in the habit of loading a table variable or temp table when I need the parsed data to persist for longer than a single query.
-- CODE TO PARSE/SORT/and re CONCATENATE A DELIMITED STRING
DECLARE @input varchar(max)
DECLARE @output varchar(max)
SET @input = 'L1,L2,L3,L5,L7,L6,L8,L4'
select @input as [before]
SET @input = ','+@input+','
-- using tally instead of XML because it parses quicker (in my time trials anyway)
;with cte (element) as
(select substring(@input,N+1,charindex(',',@input,N+1)-(N+1)) as N
from tally
where substring(@input,N,1) = ','
and N < len(@input)
)
-- xml is quicker at building the output string (in my time trials)
select @output = stuff(( SELECT ',' + element
FROM cte
ORDER BY element
FOR XML PATH('')
) ,1,1,'')
select @output as [after]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply