Answered by:
Best Method to Sort Letters in a Phrase

-
What is the best way to sort the letters in a phrase? Thanks.
Desired result for CHICAGO: ACCGHIO
Sample data:
DECLARE @Text TABLE (ID INT IDENTITY(1,1), Phrase varchar(255)); INSERT @Text(Phrase) VALUES ('CHICAGO'), ('NEW YORK'), ('HOUSTON'), ('SAN FRANCISCO'); SELECT * FROM @Text; /* 1 CHICAGO 2 NEW YORK 3 HOUSTON 4 SAN FRANCISCO */
Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
- Edited by Kalman TothModerator Sunday, August 18, 2013 3:12 PM
Question
Answers
-
I used Uri script and wrote this one:
DECLARE @Text TABLE ( ID INT IDENTITY(1, 1) , Phrase VARCHAR(255) ); INSERT @Text ( Phrase ) VALUES ( 'CHICAGO' ), ( 'NEW YORK' ), ( 'HOUSTON' ), ( 'SAN FRANCISCO' ); ; WITH base AS ( SELECT L.[char] , T.ID , T.Phrase FROM @Text 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
This still keep space character and may be its better to ignore it.
The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
My blog- Marked as answer by Kalman TothModerator Sunday, August 18, 2013 7:24 PM
All replies
-
Hi Kalman
I am not sure that understand your question... What is desired result? If you need to traverse the phrase then you probably need master..spt_values.
DECLARE @s VARCHAR(100)
SET @s = 'aaabbbbbbbccdddddee'
-- Highest frequency of same character
SELECT TOP 1 WITH TIES
[char],
COUNT(*) AS cnt
FROM (
SELECT SUBSTRING(@s, 1 + Number, 1) [char]
FROM master..spt_values
WHERE Number < DATALENGTH(@s)
AND type = 'P'
) AS q
GROUP BY [char]
ORDER BY COUNT(*) DESC
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
- Edited by Uri DimantMVP, Editor Sunday, August 18, 2013 2:38 PM
-
Hi Uri,
Desired result for CHICAGO: ACCGHIO
Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012 -
I used Uri script and wrote this one:
DECLARE @Text TABLE ( ID INT IDENTITY(1, 1) , Phrase VARCHAR(255) ); INSERT @Text ( Phrase ) VALUES ( 'CHICAGO' ), ( 'NEW YORK' ), ( 'HOUSTON' ), ( 'SAN FRANCISCO' ); ; WITH base AS ( SELECT L.[char] , T.ID , T.Phrase FROM @Text 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
This still keep space character and may be its better to ignore it.
The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
My blog- Marked as answer by Kalman TothModerator Sunday, August 18, 2013 7:24 PM
-
The best way to do this is to use the right tool. SQL is not a string language.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
- Proposed as answer by Mohamed Derhalli Sunday, August 18, 2013 5:29 PM
-
>The best way to do this is to use the right tool. SQL is not a string language.
That is true. But the language here is T-SQL, an enhanced version of SQL.
I just have no inclination of going for C# code when T-SQL can do the job so easily.
Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
- Edited by Kalman TothModerator Monday, August 19, 2013 12:01 AM