Sign in
 

none
Best Method to Sort Letters in a Phrase

 > 

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    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('')
                    ), '&#x20;', ' ') 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

    Sunday, August 18, 2013 4:33 PM
    |

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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


    Sunday, August 18, 2013 2:38 PM
    |
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    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


    Sunday, August 18, 2013 3:13 PM
    |
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote

    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('')
                    ), '&#x20;', ' ') 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

    Sunday, August 18, 2013 4:33 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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

    Sunday, August 18, 2013 4:36 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    >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



    Monday, August 19, 2013 12:00 AM
    |
    Moderator