Trailing spaces in my where clause

Question

Trailing spaces in my where clause

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
September 4, 2008 at 8:21 am

#382521

HI All,
I'm getting an unexpected result, and I'm sure it's a SET OPTION somewhere but I'm not sure which one.
my query is:
SELECT Country FROM Country WHERE Country = 'Belgium '
Which was a typo(he he he he)
However it returns a returns a row, but the actual Country = 'Belgium'
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

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

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

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

If the data type is varchar or nvarchar then SQL Server trims trailing blanks (spaces). If there were a tab or carriage return then it wouldn't trim it.

I have an article coming up sometime here on SSC about this phenomenon and it has to do with ANSI_PADDING. In leiu of the article I have a blog post about it here: http://wiseman-wiseguy.blogspot.com/2008/05/ansipadding-and-variable-length.html

Believe it or not I really hate pointing you to my blog, but since I already wrote it, why repeat 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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

thanks

WOW I'm a bit embarressed that I never knew that SQL did out RTIMMING.

Maybe it's just that the environment that I work in before at the different padding setting .

Thanks

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

HI Jack,

Interesting article thanks.

I have a few questions.

Firstly our DB has Ansi_Padding Enabled = False

I take it this is the same as SET Ansi_Padding Off ?

IF the above is true then this is back cause of my orignal post right?

What setting do I need so that 'a' != 'a '?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

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

Chris,

First we need to understand that the DB Setting is basically meaningless as the connection or session setting overrides it and all the major means of connecting to SQL Server (SSMS, ADO.NET, ODBC, OLEDB) set ANSI_PADDING on by default, thus that setting is in effect for whatever you do unless you explicitly turn it off.

Secondly for variable length character columns there is no way that I know of to make 'a' != 'a '. If you note in the table in the blog post the action taken for '=' and '!=' is the same regardless of the ANSI_PADDING setting.

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Thanks for the explanation that's perfect 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life