I have a table which have the following data
Item
......
xzypq
abdcfe
How can I sort the string in the column and get the following result?
Item
......
pqxyz
abcdef
-
Why do you want to do this in SQL? Can't you do it in your application code?shree.pat18– shree.pat182014-12-30 05:46:44 +00:00Commented Dec 30, 2014 at 5:46
-
No. As per requirement, I need it to be done in SQL itself.Shilpa Praneesh– Shilpa Praneesh2014-12-30 05:48:00 +00:00Commented Dec 30, 2014 at 5:48
-
Use select * from table_name order by Itempraveen_programmer– praveen_programmer2014-12-30 05:50:43 +00:00Commented Dec 30, 2014 at 5:50
-
There is no inbuilt function to do this. You need to write your own function. You can refer to this for one possible implementation: social.technet.microsoft.com/wiki/contents/articles/…shree.pat18– shree.pat182014-12-30 05:52:17 +00:00Commented Dec 30, 2014 at 5:52
-
Thats ordering, not sorting @praveen_programmerShilpa Praneesh– Shilpa Praneesh2014-12-30 05:56:01 +00:00Commented Dec 30, 2014 at 5:56
Add a comment
|
2 Answers
May be try the below link which might help http://social.technet.microsoft.com/wiki/contents/articles/19492.sort-letters-in-a-phrase-using-t-sql.aspx
/*Create sample table*/
IF OBJECT_ID('tempdb..#Text', 'U') IS NOT NULL
DROP TABLE #Test;
CREATE TABLE #Test
(
ID INT IDENTITY(1, 1) ,
Phrase VARCHAR(255)
);
/*Populate the table with sample data*/
INSERT #Test
( Phrase )
VALUES
( 'CHICAGO' ),
( 'NEW YORK' ),
( 'HOUSTON' ),
( 'SAN FRANCISCO' );
/*This is the final solution*/;
WITH base
AS ( SELECT L.[char] ,
T.ID ,
T.Phrase
FROM #Test T
CROSS APPLY ( SELECT SUBSTRING(T.Phrase, 1 + Number, 1) [char]
FROM master..spt_values
WHERE Number < DATALENGTH(T.Phrase)
AND type = 'P'
) L
)
SELECT DISTINCT
b1.Phrase ,
REPLACE(( SELECT '' + [char]
FROM base b2
WHERE b1.Phrase = b2.Phrase
ORDER BY [char]
FOR
XML PATH('')
), ' ', ' ') AS columns2
FROM base AS b1;
2 Comments
shree.pat18
Please add the main parts of the answer here, in case the link goes dead later.
shiljopaulson
Thanks @shree.par18 I have added it I hope this is fine :)
Using Recursive CTE also you can do this.
SELECT 'xzypq' NAME
INTO #temp
UNION ALL
SELECT 'abdcfe'
Recursive CTE
;WITH cte
AS (SELECT Cast(NAME AS VARCHAR(50)) AS st,NAME AS name1,1 AS rn
FROM #temp
UNION ALL
SELECT Cast(Substring(NAME, rn, 1) AS VARCHAR(50)),name1,rn + 1
FROM cte a
JOIN #temp b
ON a.name1 = b.NAME
AND rn < Len(a.name1) + 1)
SELECT DISTINCT (SELECT '' + st
FROM cte b
WHERE a.name1 = b.name1
AND rn <> 1
ORDER BY st
FOR XML PATH ('')) AS Ordered_String
FROM cte a
WHERE rn <> 1
Result
Ordered_String
--------------
abcdef
pqxyz
