select and spaces in end of field

[Olivier B.]

hello,

one table:

[name]
'toto'
'toto '
'toto '



i shearch the syntax for select only toto with the good number of
space after

using WHERE [name]= 'toto'
return:
'toto'
'toto '
'toto '

using WHERE [name] LIKE 'toto ' return
'toto '
'toto '

Thanks for any help.

[Erland Sommarskog]

Post by Olivier B.
[name]
'toto'
'toto '
'toto '
i shearch the syntax for select only toto with the good number of
space after
using WHERE [name]= 'toto'
'toto'
'toto '
'toto '
using WHERE [name] LIKE 'toto ' return
'toto '
'toto '

Assuming that your search string is an variable, you could do:

WHERE name = @value
AND datalength(name) = datalength(@value)

Be careful that column and variable are of the same type, that is
either both should be varchar or both should be nvarchar.

[Olivier B.]

On Wed, 12 Dec 2012 20:44:17 +0100, Erland Sommarskog

Post by Erland Sommarskog
Be careful that column and variable are of the same type, that is
either both should be varchar or both should be nvarchar.

thank you, this bypass my problem, however calculate the length in
request is an additional load for my SQL server, there is no way to
compare strictly spaces?

i forgot: sql express 2012

thanks a lot

[Erland Sommarskog]

Post by Olivier B.
thank you, this bypass my problem, however calculate the length in
request is an additional load for my SQL server, there is no way to
compare strictly spaces?

You could convert to binary, but that would be worse, as it would kill
any index on the column. I think using datalength is the best.

By the standrads for the SQL standard, trailing spaces does not count
when comparing strings.

[Olivier B.]

On Wed, 12 Dec 2012 23:40:10 +0100, Erland Sommarskog

Post by Erland Sommarskog

Post by Olivier B.
thank you, this bypass my problem, however calculate the length in
request is an additional load for my SQL server, there is no way to
compare strictly spaces?

You could convert to binary, but that would be worse, as it would kill
any index on the column. I think using datalength is the best.

ok

Post by Erland Sommarskog
By the standrads for the SQL standard, trailing spaces does not count
when comparing strings.

thanks !