Numeric String comparisons in SQL Server

Question

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.

Why? Just store it as a bigint. Or you can use numeric(38,0) if you need a range larger than -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) — Martin Smith
– Martin Smith, Commented Jun 18, 2012 at 7:19
Is this an EAV system with all kinds of things stuffed into an nvarchar(max) column? If so you could consider not doing that or using sql_variant (will sort according to datatype rules) as well as casting. — Martin Smith
– Martin Smith, Commented Jun 18, 2012 at 7:25

juergen d · Accepted Answer · 2012-06-18 07:20:47Z

2

order by cast(varchar_column as bigint)

answered Jun 18, 2012 at 7:20

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Damien_The_Unbeliever · Accepted Answer · 2012-06-18 09:37:56Z

1

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

answered Jun 18, 2012 at 9:37

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Comments

Sean · Accepted Answer · 2022-12-09 14:00:34Z

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

Collectives™ on Stack Overflow

Numeric String comparisons in SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related