How do I sort a VARCHAR column in SQL server that contains words and numbers?

Question

I have a varchar(100) field that contains both letters and numbers. The values are usually in the form of car 1, car 10, car 100, car 20. But the values can have any word preceding the number. Is there any way to sort these values numerically so that car 2 will come before car 10? Thanks.

Answer 1

Write a function that reformats the strings (e.g. car 10 becomes car 010), then use it to transform the column data in you SQL query. You'll have to check the performance of this solution.

Or... you can transform the existing data in the same way described above, so to allow alphabetical ordering in SQL without applying any functions when querying.

Answer 2

You will have to figure out the in your data and split out the car 2 into a varchar car and and int 2.

The pattern could be as simple as WORD SPACE NUMBER and you can split it based on SPACE using PATINDEX or CHARINDEX in conjunction with SUBSTRING.

Then you can sort by the two columns.

Here is a working example

SET NOCOUNT ON

Declare @Table table
(
    Id INT Identity (1, 1),
    StringValue VarChar (30)
)

INSERT INTO @Table (StringValue) VALUES ('CAR 10')
INSERT INTO @Table (StringValue) VALUES ('CAR 20')
INSERT INTO @Table (StringValue) VALUES ('CAR 2')
INSERT INTO @Table (StringValue) VALUES ('CAR 3')
INSERT INTO @Table (StringValue) VALUES ('CAR 4')

INSERT INTO @Table (StringValue) VALUES ('SHIP 32')
INSERT INTO @Table (StringValue) VALUES ('SHIP 310')
INSERT INTO @Table (StringValue) VALUES ('SHIP 320')
INSERT INTO @Table (StringValue) VALUES ('SHIP 33')
INSERT INTO @Table (StringValue) VALUES ('SHIP 34')


SELECT Id, 
    SubString (StringValue, 1, CharIndex (' ', StringValue)) ObjectName,
    CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue))) ObjectId
FROM @Table
ORDER BY 2, 3

SELECT Id, StringValue
FROM @Table
ORDER BY 
    SubString (StringValue, 1, CharIndex (' ', StringValue)),
    CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue)))

Answer 3

WITH TABLE_NAME(NAME) AS
(
SELECT 'car 20'
UNION ALL
SELECT 'car 2'
UNION ALL
SELECT 'car 10'
)
SELECT
  *
FROM TABLE_NAME
ORDER BY
SUBSTRING(NAME,1,CHARINDEX(' ',NAME,1)),CAST(SUBSTRING(NAME,CHARINDEX(' ',NAME,1)+1,100) AS INT)

SUBSTRING(NAME,1,CHARINDEX(' ',NAME,1)) - text before space CAST(SUBSTRING(NAME,CHARINDEX(' ',NAME,1)+1,100) AS INT) - text after space with cast to int for correct ordering

Answer 4

poorly and inconsistently entered data is difficult to fix programmatically. However, you should fix this data, not in your SELECT so your ORDER BY works, but in the data, so you don't have to worry about this again.

You should consider creating separate columns for the "word" and the "number" portions of the data in question. You can then run a script that tries to put the data into the proper columns, and then any necessary manual followup. You'll have the change the application logic and possibly the front end to keep the data coming into the database valid though.

Anything short of this will just result in ineffective sorting of the data.

Answer 5

How about something like:

SELECT
  col1, col1_ltrim
, col1_sort 
    = CONVERT(INT
        , SUBSTRING(col1_ltrim,1
          , ISNULL(
              NULLIF(PATINDEX('%[^0-9]%',col1_ltrim),0)-1
            , LEN(col1_ltrim)
            )
          )
        )
FROM (  
  SELECT col1
  , col1_ltrim = STUFF(col1,1,PATINDEX('%[0-9]%',col1)-1,'')
  FROM (
    SELECT 'car 505' UNION ALL
    SELECT 'car 95' UNION ALL
    SELECT 'car 8776 blue' UNION ALL
    SELECT 'car'
    ) a (col1)
  ) a
ORDER BY col1_sort

You might wrap it in a UDF for sanity:

CREATE FUNCTION dbo.StringToInt (@string VARCHAR(128))
RETURNS INT AS
BEGIN

  SELECT @string = STUFF(@string,1,PATINDEX('%[0-9]%',@string)-1,'')
  RETURN CONVERT(INT
          , SUBSTRING(@string,1
            , ISNULL(
                NULLIF(PATINDEX('%[^0-9]%',@string),0)-1
              , LEN(@string)
              )
            )
          )

END

GO

SELECT col1, col1_sort = dbo.StringToInt(col1)
FROM (
  SELECT 'car 505' UNION ALL
  SELECT 'car 95' UNION ALL
  SELECT 'car 8776 blue' UNION ALL
  SELECT 'car'
  ) a (col1)
ORDER BY col1_sort