SQL Comparing Numeric String

Question

This is the silly question but please help me out of the loop.

SELECT * 

FROM TABLE_NAME T1

WHERE '10000' > '2'

Why the condition above is false ? I know I can cast it into Number but I don'n know why the condition above is not true. Thank you very much.

iamdeowanshi · Accepted Answer · 2017-10-05 02:01:38Z

2

If you try

WHERE 'A' < 'B'

it gives you true

If you try

WHERE 'AAAAAB' > 'AAAAAA'

it returns true ,since B is greater than A

For the same reason WHERE '10000' > '2' return false

answered Oct 5, 2017 at 2:01

iamdeowanshi

1,08911 silver badges19 bronze badges

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

3 Comments

Pham Ngoc Vinh Over a year ago

Thank you, but then again. Why '30000' < '2' is FALSE. I think they may compare the first char then ignore the rest ???

iamdeowanshi Over a year ago

It compare first character of '3000' which is 3 with first character of '2' which 2, Now 3 < 2 return false because ASCII code of 2 is less than 3

zaTricky Over a year ago

@iamdeowanshi the comment you gave here is the real reason ; I suggest, especially since this is already the accepted answer, to add that to the actual answer. ABC is alphabetically before DEFG ; but clearly a four-digit number is greater than a 3-digit number. Alphabetically we go by the first letter first, whereas numerically we would pad leading zeroes before doing such a comparison.

Gordon Linoff · Accepted Answer · 2017-10-05 01:49:06Z

2

The condition is false because the values are compared as strings, not numbers. The same reason that:

WHERE 'A0000' > 'B'

evaluates to false.

The moral? Only use single quotes for string and date constants. Do not use single quotes for numbers.

answered Oct 5, 2017 at 1:49

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Pham Ngoc Vinh Over a year ago

Thank you for your reply but I have a further question. Then why the 'A0000' < 'B' return TRUE ? I want to know how they compared with each other.

Gordon Linoff Over a year ago

@PhamNgocVinh . . . Because the comparison is alphabetical, like entries in a dictionary.

멍개-mung · Accepted Answer · 2017-10-05 02:06:58Z

1

Numeric types and strings are treated differently.

example)

1 < 2 is TRUE

'1000'>'2' is FALSE

The character type changes to a numeric type when compared.

using 49 instead of '1' and using 50 instead of '2'

result 49 > 50 = FALSE

if '1000' > '1', is TRUE

See the ASCII code.

answered Oct 5, 2017 at 2:06

멍개-mung

4765 silver badges12 bronze badges

Comments

Berk Askin · Accepted Answer · 2017-10-05 02:27:58Z

1

Just think simply. As it is all, Char type have values on ascii system or charset collation.

Just for example :

Declare @a char(1)='A',@b char(1)=B,@c char(1)='C'

a value is 1 b value is 2 c value is 3

It's just alphabetically order for char based variables on Sql server.

This means if you compare char, nchar, varchar or nvarchar values, program first of all examines value your first index, after then second... Till that end.

answered Oct 5, 2017 at 2:27

Berk Askin

1771 silver badge10 bronze badges

Collectives™ on Stack Overflow

SQL Comparing Numeric String

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related