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

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.

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

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.

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.

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

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

You can take advantage of the fact that

patindex('%...', _col_)

matches the last character and

patindex('%...%', _col_)

matches any number of characters. So as long as the digits are at the end of the column value, you can easily extract the numeric value from the column as such (StackOverflow will not let me put the word UNION in a post - replace UUU with UNION):

select Rec,
case
   when patindex('%[0-9]', Rec) > 0
   then left(Rec, patindex('%[0-9]%', Rec)-1)
   else Rec
end,
case
   when patindex('%[0-9]', Rec) > 0
   then cast(right(Rec, len(Rec)-patindex('%[0-9]%', Rec)+1) as int)
end
from (select 'SHIP34' Rec UUU select 'SHIP 33' UUU select 'SHIP 320' UUU select 'SHIP310' UUU select 'SHIP32' UUU select 'CAR 4X' UUU select 'CAR 4' UUU select 'CAR3' UUU select 'CAR 2' UUU select 'CAR20' UUU select 'CAR 10') TestData
order by
   case
      when patindex('%[0-9]', Rec) > 0
      then left(Rec, patindex('%[0-9]%', Rec)-1)
      else Rec
   end,
   case
      when patindex('%[0-9]', Rec) > 0
      then cast(right(Rec, len(Rec)-patindex('%[0-9]%', Rec)+1) as int)
   end