• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

Trailing Spaces = Trailing Nothings

All -

I have a two rows of data in which one of the columns has the following data :

'1234567'
'1234567       ' (Seven trailing spaces)

When I run a query against the Column_name = '1234567', it returns both of the rows - SHould it?
When I run a query against the Column_name like '1234567 %', it returns the rows with trailing spaces - Which makes sense.

When I run a query against the LEN(Column_name) - it both returns '7' which I am assuming LEN function does not count the trailing spaces - please confirm.

0
up1973
Asked:
up1973
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
check this one:
SELECT DATALENGTH(Column_name) 

Open in new window

0
 
jose_juanCommented:
Hi,

LEN function convert explicitly your parameter to nvarchar and to varchar datatypes conversion truncate final spaces. This is the reason.

Warning! if you use DATALENGTH this function return the bytes of the data expression, different

A trivial example

    SELECT datalength( N' ' ), datalength( ' ' )

return 2 and 1 length. But... What do you try compare?

Regards!

0
 
up1973Author Commented:
That help in understanding the data length is sql server - also is

'1234567' = '1234567       ' (seven spaces)?

I have a two rows of data in which one of the columns has the following data :

'1234567'
'1234567       ' (Seven trailing spaces)

When I run a query against the Column_name = '1234567', it returns both of the rows - SHould it?
When I run a query against the Column_name like '1234567 %', it returns the rows with trailing spaces - Which makes sense.

thanks  for your help.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jose_juanCommented:
Hi,

the best way for understand it is read documentation of your SQL version.

then, try examples

set nocount on

declare @a nvarchar(100)
declare @b varchar(100)

set @a = N'1234    '
set @b = '1234    '

select dlA = datalength( @a ), dlB = datalength( @b )
select lA = len( @a ), lB = len( @b )

if @a = @b
 print 'Equals'
else
 print 'Not equals'

Results

dlA         dlB        
----------- -----------
16          8

lA          lB          
----------- -----------
4           4

Equals

...

What do you try do?
@a must be equal or different to @b? (on your predicate of course)

Regards!
0
 
Scott PletcherSenior DBACommented:
>> Column_name = '1234567', it returns both of the rows - SHould it? <<

Yes.  Spaces is the standard padding character, so the two values can legitimately be considered equal.


>> LEN(Column_name) for both returns '7', [so] I am assuming LEN function does not count the trailing spaces <<

Correct.  For some reason, LEN() ignores trailing blanks.
0
 
up1973Author Commented:
thank you - that answers my question
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think, this should have been a point split with jose_juan, who explained the "why":
LEN function convert explicitly your parameter to nvarchar and to varchar datatypes conversion truncate final spaces. This is the reason.

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now