CODEWITHSUNDEEP
sqlsql-server
user978122
user978122

Reputation: 5761

Numeric String comparisons in SQL Server

I have a rather silly question about SQL Server. I am trying to store a BigInt in SQL Server, and figure that I can convert it to a string before inserting it into a Nvarchar(Max)-style field.

However, I need to run a SELECT statement against that column, and as it's a string, I am not sure how to evaluate it. I am aware that strings in some languages do evaluate numerically, such that '234234' is considered lesser than '2342545345344'. Would it be possible to do something similar here?

Edit: Blast, I'm an idiot. I meant to say C#'s BigInteger, which can hold values much larger than SQL's BigInt.

Upvotes: 1

Views: 846

Answers (3)

Sean
Sean

Reputation: 91

It seems like you are looking for how to sort these in order?

If you have a trimmed text string that is only "0-9" and prefixed with "-" when negative, then you can use the below to do a character-based sort. Numbers with fewer digits are padded, and negative numbers are translated to an inversely sorted character set. Note, you need to have a conceptual "maximum length".

DECLARE @maxlen INT = 1000
DECLARE @abc123 TABLE (NumericString VARCHAR(1000)) --e.g. "Big Integer"

INSERT INTO @abc123
          SELECT '123'
UNION ALL SELECT '-123' 
UNION ALL SELECT '1789' 
UNION ALL SELECT '2345' 
UNION ALL SELECT '-3490' 
UNION ALL SELECT '-6888' 
UNION ALL SELECT '-1000000' 
UNION ALL SELECT '0'

SELECT aa.NumericString FROM @abc123 aa
CROSS APPLY (SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
aa.NumericString,'0','z'),'1','y'),'2','x'),'3','w'),'4','v'),'5','u'),'6','t'),'7','s'),'8','r'),'9','q') [translatedDesc]
) ss --todo: you can use TRANSLATE() here if you have the option. 
CROSS APPLY (SELECT
 CASE WHEN  LEFT(aa.NumericString,1) = '-' THEN 
    CONCAT('-',
    REPLICATE('z',@maxlen-LEN(aa.NumericString)-1),
    REPLACE(ss.translatedDesc,'-','') ) --each character is translated to a character subset where a negative 0-9 would sort in reverse order, while SQL server sorts "ASC"
   ELSE 
    CONCAT('_', -- a "positive" indicator that sorts higher than '-' or '0'
    REPLICATE('0',@maxlen-LEN(aa.NumericString)),
    aa.NumericString)  
   END [string2]
) uu
ORDER BY 
uu.string2 COLLATE Latin1_General_BIN2 ASC

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

What type of SELECT will you be performing? As a quick first step (assuming all values are positive, and stored as a sequence of digits (no scientific notation or sign symbols)), you can compare the length of the two strings.

The shorter string is the lesser of the two. If the two strings are the same length, then the lesser string (alphabetically) is the lesser numerically.

So, to output 0 for a is less than b, 1 for equal, 2 for a being greater than b:

CASE
  WHEN LEN(a) < LEN(b) THEN 0
  WHEN LEN(a) > LEN(b) THEN 2
  WHEN a < b THEN 0
  WHEN a > b THEN 2
  ELSE 1
END

Upvotes: 1

juergen d
juergen d

Reputation: 204746

order by cast(varchar_column as bigint)

Upvotes: 2

Related Questions