none
Best Method to Sort Letters in a Phrase

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

  • 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
  • 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
  • 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
  • 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
  • >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