Geek Answers Handbook

How to sort a VARCHAR column on an SQL server that contains words and numbers?

I have a varchar (100) field containing both letters and numbers. 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 a way to sort these values ​​numerically so that car 2 comes in front of car 10? Thank.

0
source share
6 answers

You will need to find the data in your data and split it car 2into varchar carand and int 2.

A template can be as simple as it is WORD SPACE NUMBER, and you can break it down based SPACEon using PATINDEXor CHARINDEXin combination with SUBSTRING.

Then you can sort by 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)))
+1
source

Bad and inconsistent input is difficult to fix programmatically. However, you must correct this data, not in your SELECT, so that your ORDER BY works, but in the data, so you no longer need to worry about it.

"" "" . script, , . , , , , , .

, , .

+1

, (, 10 010), SQL-. .

... , , SQL - .

0
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)) - CAST (SUBSTRING (NAME, CHARINDEX ('', NAME, 1) +1,100) AS INT) - int

0

- :

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

UDF :

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
0

,

patindex('%...', _col_)


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

matches any number of characters. 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 allow me to put the word UNION in the UUU post-replacement using 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
0
source

More articles:


All Articles