Sorting a field

  • I have   table in which the accountnumber will contain both numeric and character data. When displaying in my grid, i need to check if it is a numeric data i have to sort it numerically and ignore if it is character data.

    Kindly help


    Kindest Regards,

    sree

  • I was VERY errant in not noting who wrote this to give proper credit!  I got this off a posting on this site.  If you wrote it, please respond so I can update my script. 

    Here is a possible solution.  It sorts on Numeric first, then by Alpha. 

    CREATE FUNCTION [dbo].[nmbPadd]( @mystr varchar(20))

    RETURNS varchar(4000)

    AS 

    BEGIN

         DECLARE @StartNmb integer

         DECLARE @StopNmb integer

         DECLARE @pos integer

         DECLARE @isNumb bit

         DECLARE @myLen integer

         DECLARE @rez varchar(4000)

         IF( @myStr IS NULL OR LEN( @myStr) = 0) RETURN ''

         SET @rez = ''

         SET @myLen = LEN( @mystr)

         SET @isNumb = 0

         SET @pos = 1

         WHILE( @pos <= @myLen)

         BEGIN

              IF( CHARINDEX( SUBSTRING( @mystr, @pos, 1), '0123456789') > 0)

              BEGIN

                   --check to see if the prev char was numeric

                   IF( @isNumb = 1)

                   BEGIN

                        SET @StopNmb = @StopNmb + 1

                   END

                   ELSE

                   BEGIN -- prev char was NOT numeric

                        SET @StartNmb = @pos

                        SET @StopNmb = 1

                   END

              SET @isNumb = 1

              END

              ELSE -- our current char is not numeric

              BEGIN

                   -- check to see if the prev char was numeric

                   IF( @isNumb = 1)

                   BEGIN

                        -- 10 is max number of digitis that a number can have

                        SET @rez = @rez + REPLICATE( '0', 10 - @StopNmb) + SUBSTRING( @myStr, @StartNmb, @StopNmb + 1)

                   END

                   ELSE

                   BEGIN -- prev char was NOT numeric

                        SET @rez = @rez + SUBSTRING( @mystr, @pos, 1)

                   END

                   SET @isNumb = 0

              END

              SET @pos = @pos + 1

         END

         IF( @isNumb = 1)

              SET @rez = @rez + REPLICATE( '0', 10 - @StopNmb) + SUBSTRING( @myStr, @StartNmb, @StopNmb)

              RETURN @rez

    END

    GO

    DECLARE @t TABLE( sort varchar(20))

    INSERT INTO @t

    SELECT '1dd' UNION ALL

    SELECT '1' UNION ALL

    SELECT '1x4' UNION ALL

    SELECT '1cc2' UNION ALL

    SELECT '1110-345-720a3' UNION ALL

    SELECT '11' UNION ALL

    SELECT '380-41-3a' UNION ALL

    SELECT '10'

    SELECT sort FROM @t

    ORDER BY [dbo].[nmbPadd](sort)

    DROP FUNCTION [dbo].[nmbPadd]

    I wasn't born stupid - I had to study.

  • Here's a good thread to reference....

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=328228

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Way better!  Or, is that way more better?   

    Thanks John!

    I wasn't born stupid - I had to study.

  • Hmmm...

    My script returns:

    sort                

    --------------------

    1

    1cc2

    1dd

    1x4

    10

    11

    380-41-3a

    1110-345-720a3

    The Robert Davis script returns:

    sort                

    --------------------

    1x4

    1cc2

    1110-345-720a3

    1dd

    380-41-3a

    1

    10

    11

    I think the latter is not as good...

    I wasn't born stupid - I had to study.

  • Sree,

    Rather than us guess, do you have some samples of the data before and after the sort you'd like?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This could be another way, dont know how much performance you gonna get out of it.

    Here's the data in table1

    data

    1dd

    1

    1x4

    1cc2

    1110-345-720a3

    11

    380-41-3a

    10

    12983

    21

    19

    --------------------------------

    SELECT data,isnumeric(data)

    FROM dbo.table1

    ORDER BY CASE WHEN ISNUMERIC(data) = 1 THEN CAST(data AS INT) END ASC,

                  CASE WHEN ISNUMERIC(data) = 0 THEN data END ASC

    Result:

    data               isnumeric(data)

    1110-345-720a3      0

    1cc2                     0

    1dd                       0

    1x4                       0

    380-41-3a              0

    1                          1

    10                         1

    11                         1

    19                         1

    21                         1

    12983                    1

    Thanks,

    Dinesh.

  • Dinesh, replace "1dd" with "1d3" int your data set and rerun your query.

    See what happens.

    _____________
    Code for TallyGenerator

  • Do you mean like this when you say to "ignore if is character data"???

    DECLARE @t TABLE( sort varchar(20))

    INSERT INTO @t

    SELECT '1dd' UNION ALL

    SELECT '1x4' UNION ALL

    SELECT '1d3' UNION ALL

    SELECT '1cc2' UNION ALL

    SELECT '100' UNION ALL

    SELECT '1110-345-720a3' UNION ALL

    SELECT '11' UNION ALL

    SELECT '380-41-3a' UNION ALL

    SELECT '1' UNION ALL

    SELECT '10'

     SELECT *

      FROM @t

     WHERE Sort NOT LIKE '%[^0-9]%'

     ORDER BY CAST(Sort AS INT)

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sergiy, that was a good catch but there are very few instances where we get that kind of a sequence where d --> decimal should be considered as a string. Still its a bug for the above script and this is how i fixed it.

    This function seperates numbers from strings

    ---------------------------------------------------------------

    CREATE FUNCTION dbo.fn_seperate_num_alpha(@string varchar(100))

    RETURNS bit

    AS

    BEGIN

    DECLARE @position int,@OUT INT,@OUTP BIT

    -- Initialize the current position and the string variables.

    SET @position = 1

    SET @OUTP = 1

    WHILE @POSITION <= LEN(@STRING)

       BEGIN

        -- Check if each letter in the string is number or not

        IF (ASCII(SUBSTRING(@string, @position, 1))) BETWEEN 48 AND 57

            SET @OUT = 1

        ELSE

            SET @OUT = 0

        SET @position = @position + 1

        SET @OUTP = @OUTP & @OUT

       END

    -- returns 1 if it is number, returns 0 if there is any characters apart from numbers

    RETURN @OUTP

    END

    --------------------------------------------------------------------------------

    This query returns the result set

    --------------------------------------------------------------------------------

    SELECT data,dbo.fn_seperate_num_alpha(data)

    FROM dbo.t1

    ORDER BY CASE WHEN dbo.fn_seperate_num_alpha(data) = 1 THEN CAST(data AS INT) END ASC,

                  CASE WHEN dbo.fn_seperate_num_alpha(data) = 0 THEN data END ASC

    --------------------------------------------------------------------------------

  • Dinesh,

    Jeff posted more clear and effective solution just 1 post above.

    Actually he has to post this solution twice a week, because people don't bother to read previous postings.

    CASE WHEN Data NOT LIKE '%[^0-9]%' THEN CAST(data AS INT) END

    No UDF required.

    _____________
    Code for TallyGenerator

  • Heh...

    Dinesh... take a look at the following for a complete explanation as to why ISNUMERIC should never be treated as ISALLDIGITS... and, it's not a fault... it's a feature

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=243646&p=2

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hai Jeff,

    The data in that field would be like

    0000022211

    0000022243

    0000072211

    0000102221

    AB00011

    CD000111

    The kind of output i would like to have is:

    22211

    22243

    72211

    102221

    AB00011

    CD000111

    The field has '0' in front to convert it to 10 digits . If it is a numeric one it has to trim zero's just display the numeric values and sort them numerically and if it has character it has to display as it is . I need a query that would return this o/p as i have to use this in a view . If i use the query tht dinesh asked to use it would return like

    102221 

    22211

    22243

    72211

    AB00011

    CD000111 which i dont want. Hope i'm more clear now.

     

     


    Kindest Regards,

    sree

  • Perfectly clear, thank you...

     DECLARE @TestData TABLE (StringValue VARCHAR(20))

     INSERT INTO @TestData

     SELECT '0000022211' UNION ALL

     SELECT '0000022243' UNION ALL

     SELECT '0000072211' UNION ALL

     SELECT '0000102221' UNION ALL

     SELECT 'AB00011'    UNION ALL

     SELECT 'CD000111'

     SELECT CASE

                WHEN StringValue LIKE '[^0-9]%'

                THEN StringValue

                ELSE CAST(CAST(StringValue AS INT) AS VARCHAR(20))

            END AS SortedString

       FROM @TestData

      ORDER BY CASE

                   WHEN StringValue LIKE '[^0-9]%'

                   THEN StringValue

                   ELSE STR(CAST(StringValue AS INT),20)

               END

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Jeff,

    Thanks a lot!! . I got it working

     


    Kindest Regards,

    sree

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply