Weird RTRIM applied when querying string columns, is there a setting for this?

Question

Post reply

Weird RTRIM applied when querying string columns, is there a setting for this?

J-F Bergeron

SSCrazy Eights

Points: 8593
More actions
February 4, 2010 at 9:01 am

#156646

Hi all,
This morning I came across a weird discovery. There seems to be something in SQL server that permits the comparison of strings, even if they have trailing spaces. This seems to happen in both the value in the table, or the value passed in the where statement.
For sake of clarification, here's a little test I came up with:
--=== Clean ourselves up
IF EXISTS ( SELECT 1
WHERE OBJECT_ID('TestSpace') IS NOT NULL )
DROP TABLE TestSpace
--=== Create the testSpace table
CREATE TABLE TestSpace
(
ID INT NULL ,
VAL VARCHAR(30) NULL
)
--=== Feed the different testing values
INSERT INTO testSpace
( ID ,
val
)
SELECT 1 ,
'testSpace'
UNION ALL
SELECT 2 ,
'testSpace '
UNION ALL
SELECT 3 ,
'test Space'
UNION ALL
SELECT 4 ,
' testSpace '
UNION ALL
SELECT 5 ,
' testSpace'
--=== Returns 1 and 2
SELECT *
FROM testSpace
WHERE val = 'testSpace '
--=== Also returns 1 and 2
SELECT *
FROM testSpace
WHERE val = 'testSpace'
--=== Both the value sent in the where clause, and the value in the table seem to be applied with a RTRIM() function,
-- and they both magically return the same rows.
-- Is there a setting on the server to ensure this does not happen?
Is there a setting for this in SQL server that I'm not aware of? To ensure this does not happen.
Thanks,
Cheers,
J-F

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 1

That is correct, SQL will ignore trailing whitespaces.

http://support.microsoft.com/default.aspx/kb/316626

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

Thanks for the info, seems I never got a situation where I could test this. This sounds a bit weird to me, but in the end, it does make sense.

I guess there are no parameters you can change, from what I can read.

Thanks again for the link,

Cheers,

J-F

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 3

If you want to compare the strings and ensure trailing whitespace is taken into account, check the field with a REVERSE.

REVERSE will make the trailing whitespace into leading whitespace and then it'll be taken into account.

CREATE Table #Checker

(whitespace varchar(50))

INSERT INTO #Checker VALUES('text ')

SELECT whitespace FROM #Checker

WHERE REVERSE(whitespace) = REVERSE('text ')

DROP TABLE #Checker

This will only return the entry with the desired number of spaces

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 4

Another way to do the comparison is to cast the values to VARBINARY.

Also, a LIKE comparison will be "closer", but it is not 100% either.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Nope there is not a setting to have SQL Server include white space when doing string comparison. Here's an article[/url] I wrote that sheds some light on it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Stefan Krzywicki (2/4/2010)
If you want to compare the strings and ensure trailing whitespace is taken into account, check the field with a REVERSE.
REVERSE will make the trailing whitespace into leading whitespace and then it'll be taken into account.
CREATE Table #Checker
(whitespace varchar(50))
INSERT INTO #Checker VALUES('text ')
INSERT INTO #Checker VALUES('text ')
SELECT whitespace FROM #Checker
WHERE REVERSE(whitespace) = REVERSE('text ')
DROP TABLE #Checker
This will only return the entry with the desired number of spaces

REVERSE is a fairly expensive function. It would be better to compare the two values and the DATALENGTH of the two values. DATALENGTH will include trailing blanks in its count.

--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)