String compare: 'a' = 'a' ==>True or False?

Question

Post reply

String compare: 'a' = 'a' ==>True or False?

sol.nt

SSCrazy

Points: 2062
More actions
July 15, 2008 at 3:06 am

#381667

Hi all
I have a query which compare two string type "nvarchar"
Something like this:
if @a = @b-2
print 'True'
else print 'False'
It is simple, but when i input something like
@a='1'
@b-2='1 ' ( with some blank space )
Then the query is still return "True" althought what i mean is "False"
I googled this and see that sql server auto insert some character to sorter string(in many case, it is a blank space,correct me if i am wrong :)) before compare those string.
So, have you guys have any idea to solve this.
Thanks very much.
Sol 😀

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 1

Hi Sol,

Try this

DECLARE @a varchar(10),@b varchar(10)

SET @a='1'

SET @b-2='1 '

if QUOTENAME(@a) = QUOTENAME(@b)

print 'True'

else

print 'False'

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SQL ignores trailing spaces when comparing strings. It's probably because of the char data type, which is space padded to max size

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

It is part of the ANSI standard to ignore these trailing spaces. It is done automatically in joins as well. If you find yourself in a situation in which you care about trailing spaces you should try to re-think the design.

sol.nt SSCrazy Points: 2062 More actions · Answer 4

To @hari.Shama:

Thanks for your help.

I checked and it worked but it seem confuses to me when use this one.

To @gilamonster and @michael-2 Earl:

Yeah, You're right.

Thanks for all of your help.

To resolve this, i use "datalength" function which return length of string include space, so i can compare like this:

If Datalength('1') = Datalength('1 ')

print 'True'

elseprint 'False'

Thanks and regards.

Sol

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 5

I myself had this issue. And aftere searching here and there, I found that the equality (=) operator elininates the trailing spaces and then compare. If you want to compare the two variables with the trailing spaces, use LIKE operator.

i.e....

Declare @a varchar(10)

Declare @b-2 varchar(10)

Select @a = '1'

Select @b-2 = '1 '

if @a = @b-2

print 'True'

else

print 'False'

--Will Print True

if @a LIKE @b-2

print 'True'

else

print 'False'

--Will Print False

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Sanchita Old Hand Points: 369 More actions · Answer 6

I too think 'Like' is the best solution as suggested above.

harshil Mr or Mrs. 500 Points: 590 More actions · Answer 7

You can also do this 🙂

if ('c'+@a+'c') = ('c'+@b+'c' )

print 'True'

else print 'False'

Raja Zahid Rashid SSC Rookie Points: 46 More actions · Answer 8

I was just trying and found that case sensitivity and space problem can be solved at the same time by the following code.

DECLARE @a varchar(10),@b varchar(10)

SET @a='a '

SET @b-2='A '

if LEN(@a) = LEN(@b)

AND

QUOTENAME (@a) =QUOTENAME (@b)

AND

ASCII(@a) = ASCII(@b)

print 'True'

else

print 'False'

sol.nt SSCrazy Points: 2062 More actions · Answer 9

Personaly, I don't think use "like" is a good idea.

Because user can input "%" or "*" in their input string and so, your condition will fail to compare these values.

Sol

thegreatsamarth SSChasing Mays Points: 608 More actions · Answer 10

This can also work... hav a look:

DECLARE @a VARCHAR(10)

DECLARE @b-2 VARCHAR(10)

SET @a = 'ab'

SET @b-2 = 'ab '

IF REVERSE(@a)= REVERSE(@b)

PRINT 'Equal'

ELSE

PRINT 'Not Equal'

-----------------------------------

OUTPUT:

-----------------------------------

Not Equal

Sanchita Old Hand Points: 369 More actions · Answer 11

sol (7/16/2008)
Personaly, I don't think use "like" is a good idea.
Because user can input "%" or "*" in their input string and so, your condition will fail to compare these values.
Sol

to clarify - when you have just spaces like in ur case the question asked, 'like' is a good solution.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 12

Hari.Sharma (7/15/2008)
Hi Sol,
Try this
DECLARE @a varchar(10),@b varchar(10)
SET @a='1'
SET @b-2='1 '
if QUOTENAME(@a) = QUOTENAME(@b)
print 'True'
else
print 'False'

Seems like the easiest way to go to me! 😉

Now, the real question is why anyone would be storing trailing blanks for anything...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sol.nt SSCrazy Points: 2062 More actions · Answer 13

to clarify - when you have just spaces like in ur case the question asked, 'like' is a good solution.

Yeah, I understood what you mean.

Regards

Sol