ORDER BY varchar data type

  • I have a field who's data type is varchar(4). Currently in this field their are numbers but this will change in the future and will hold combinations of numbers and letters. However, I need to get it to sort properly. For example, 1,2,3,4,5,6,7,8,9,10,11,12.....does not order in this manner. It orders like this 1,10,11,12,13,14,15,16,17,18,19,2,21,22,23,24...etc. I need it to order like the first example. There are also values of 3A,3B etc which need to come after 3. I have seen this done before but can not find the proper T-SQL to obtain the desired sort order. Thanks in adavance.

    I code therefore I am.....

  • Funny you should mention this today.  I got something from Frank Kalis who get it from someone else; but it is pretty slick.  It is a function which removes characters.  For your example, I used it in the ORDER BY statement. 

    See if this helps you. 

    CREATE FUNCTION dbo.RemoveChars( @Input varchar(1000))

    RETURNS VARCHAR(100)

    BEGIN

     DECLARE @pos int

     SET @Pos = PATINDEX( '%[^0-9]%', @Input) 

     WHILE @Pos > 0  

     BEGIN

      SET @Input = STUFF( @Input, @pos, 1, '') 

      SET @Pos = PATINDEX( '%[^0-9]%', @Input)

     END

     RETURN @Input

    END

    GO

    CREATE TABLE #Test( [Column] varchar(10))

    INSERT INTO #Test SELECT '11' AS 'Column'

    INSERT INTO #Test SELECT '10' AS 'Column'

    INSERT INTO #Test SELECT '2' AS 'Column'

    INSERT INTO #Test SELECT '3' AS 'Column'

    INSERT INTO #Test SELECT '1' AS 'Column'

    INSERT INTO #Test SELECT '3A' AS 'Column'

    INSERT INTO #Test SELECT '3B' AS 'Column'

    INSERT INTO #Test SELECT '3C' AS 'Column'

    SELECT * FROM #Test ORDER BY CONVERT( integer, dbo.RemoveChars( [Column])) ASC

    DROP TABLE #Test

    DROP FUNCTION dbo.RemoveChars

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

  • dag gum! That worked great!!! Thanks!

     

    I code therefore I am....

     

  • May I add, that I strongly would consider having two columns instead on one?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • This doesn't work if the inserts are not done in order:

    INSERT INTO #Test Select '2B' as 'Column'

    INSERT INTO #Test select '2A' as 'Column'

    SELECT * FROM #Test ORDER BY CONVERT( integer, dbo.RemoveChars( [Column])) ASC

    Column    

    ----------

    1

    2

    2B

    2A

    3

    3A

    3B

    3C

    10

    11

    This is probably one of those things that you either follow Frank's suggestion about splitting into two fields or have the client do the work.  I think it would be possible to use this approach by adding another function to return only the character data and then a 2 field order by.  But depending on your application, this is a lot of overhead.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • I went back an tested it in the application I am using it for. I have numbers from 1 to 70 with some As and Bs(34A,34B) thrown in here and there. I added a few that would make things out of order from when they were inserted(9A, 13A). The result was that the field that I am ordering by( I put the function in the order) was sorted appropriatly. I will let you know if things get wacky as I move forward and more data is entered.

    Thank you.

     

    I code therefore I am...

  • Probably too late but I posted this solution in another thread with the same question

    order by cast(

    (case when patindex('%[a-z]%',[col]) = 0

    then [col]

    else left([col],patindex('%[a-z]%',[col])-1)

    end) as int),[col]

    Far away is close at hand in the images of elsewhere.
    Anon.

  • How about this:

    CREATE TABLE #Test

    (

      col varchar(10)

    )

    SET NOCOUNT ON

    INSERT #test VALUES ('11')

    INSERT #test VALUES ('10')

    INSERT #test VALUES ('2')

    INSERT #test VALUES ('3')

    INSERT #test VALUES ('1')

    INSERT #test VALUES ('3C')

    INSERT #test VALUES ('3B')

    INSERT #test VALUES ('3A')

    INSERT #test VALUES ('2B')

    INSERT #test VALUES ('2A')

    SET NOCOUNT OFf

    SELECT *

      FROM #test

     ORDER BY Right( Replicate('0', 10) + col, 10)

    DROP TABLE #Test

     

  • But what when you have data like A2 or the like? This should also be taken into account.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Obviously, there are many ways to do this. Still another method:

    CREATE FUNCTION dbo.PadLeft

    (

      @theField varchar(1000),

      @padLen smallint,

      @padChar varchar(1)

    )

    RETURNS varchar(1000)

    AS

    BEGIN

      RETURN Right(Replicate(@padChar, @padLen) + @theField, @padLen)

    END

    GO

    CREATE TABLE #Test

    (

      col varchar(10)

    )

    SET NOCOUNT ON

    INSERT #test VALUES ('11')

    INSERT #test VALUES ('10')

    INSERT #test VALUES ('2')

    INSERT #test VALUES ('3')

    INSERT #test VALUES ('1')

    INSERT #test VALUES ('3C')

    INSERT #test VALUES ('3B')

    INSERT #test VALUES ('3A')

    INSERT #test VALUES ('2B')

    INSERT #test VALUES ('2A')

    SET NOCOUNT OFF

    SELECT *

      FROM #test

     ORDER BY dbo.PadLeft(col, 10, '0')

    DROP TABLE #Test

    GO

  • Sorry mkeast,

    Didn't work the way you expected... here's the output.

    col       

    ----------

    1

    2

    3

    10

    11

    2A

    2B

    3A

    3B

    3C

    I think the original poster wants...

    col       

    ----------

    1

    2

    2A

    2B

    3

    3A

    3B

    3C

    10

    11

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

  • hey come on frank, stop stirring it

    That combination was not asked for

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Frank, I understand the issue you bring up. It depends on the business rules for that the particular application. I think the method that you originally posted requires that non-numeric characters be ignored. In that case, I would use the RemoveChars() UDF.  My method assumes numbers followed by letters.  I was really addressing the followup question.

    The requirements really haven't been fully stated here that account for the various combinations (numbers only, non-numbers only, numbers followed by non-numbers, or any mix of characters, etc).

    Again, it all depends on the requirements.

     

  • Jeff, you are correct. That's what happens when Frank gets everybody excited about an issue. Jumped the gun a bit on that. 

  • okay,okay... I don't want to be branded as troublemaker.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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