CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
user2041076
user2041076

Reputation: 119

How to compare two strings in SQL Server 2008 R2

If I had something like R11 and R2, I would expect R2 to be less than R11 but it’s not:

select 
    case when 'R11' < 'R2'
      then 'YES'
      else 'NO'
    end

Produces a YES when it should be no.

Is there a easier way to do this besides making a CLR function?

Upvotes: 1

Views: 1440

Answers (2)

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

is this what your looking for, sorting numeric part of the string as int and character part as character. Hope this is helpful.

Considerations: Total length 5, 1'st character [a-z], 2-5 characters [0-9]

DECLARE @TestTable TABLE(ID INT IDENTITY(1,1), String VARCHAR(5))
INSERT INTO @TestTable
VALUES('R1'),('R11'),('R12'),('R2'),('R21'),('Q12'),('U2'),('R21')

SELECT
    tt.ID,
    tt.String
FROM
    @TestTable tt
ORDER BY LEFT(tt.String,1),CAST(STUFF(tt.String,1,1,'') AS INT)

Upvotes: 1

Marcel N.
Marcel N.

Reputation: 13976

Alphanumeric sorting is not the same as natural sorting. If you want to avoid a CLR and if the input data is always in that format (Rxxx) then you can just remove the first letter and compare two INTs:

DECLARE @Index1 AS INT
DECLARE @Index2 AS INT

DECLARE @StrIndex1 AS VARCHAR(10)
DECLARE @StrIndex2 AS VARCHAR(10)

SET @StrIndex1 = 'R11'
SET @StrIndex2 = 'R2'

SET @Index1 = CAST(RIGHT(@StrIndex1, LEN(@StrIndex1) - 1) AS INT)
SET @Index1 = CAST(RIGHT(@StrIndex2, LEN(@StrIndex2) - 1) AS INT)

SELECT 
    CASE when @Index1 < @Index2
      THEN 'YES'
      ELSE 'NO'
    END

Upvotes: 0

Related Questions