right blank space in sql

I need ti getthe length of a string like this (varchar or char doesn't matte
r)
'AB12345678A '. The length is 14, but the function len('AB12345678A ')
return 11. Len doesn't count the blank spaces. How con i get yhe length of m
y
strings including blank spaces?Hi
It depends on the ANSI_PADDING setting for you DB, but LEN will always
return the number of characters, right trimmed.
If ANSI_PADDING is ON when the data it put into the DB, you could use this
work around:
SELECT LEN('AB12345678A ' + '.') -1
This will cause a "." (or any other character) to be appended after the
blanks, so LEN will not trim it, then subtract one from the total count to
compensate for the "."
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:F8C0249C-6E56-465A-995E-A0AF0701F8F8@.microsoft.com...
> I need ti getthe length of a string like this (varchar or char doesn't
matter)
> 'AB12345678A '. The length is 14, but the function len('AB12345678A ')
> return 11. Len doesn't count the blank spaces. How con i get yhe length of
my
> strings including blank spaces?|||Hi,
I have used Datalength fucntion it gives me value 14.
Read BOL for Datalength and Len function
Thanks
AM
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%233v1nu5KFHA.2796@.tk2msftngp13.phx.gbl...
> Hi
> It depends on the ANSI_PADDING setting for you DB, but LEN will always
> return the number of characters, right trimmed.
> If ANSI_PADDING is ON when the data it put into the DB, you could use this
> work around:
> SELECT LEN('AB12345678A ' + '.') -1
> This will cause a "." (or any other character) to be appended after the
> blanks, so LEN will not trim it, then subtract one from the total count to
> compensate for the "."
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:F8C0249C-6E56-465A-995E-A0AF0701F8F8@.microsoft.com...
> matter)
')
of
> my
>|||"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:F8C0249C-6E56-465A-995E-A0AF0701F8F8@.microsoft.com...
>I need ti getthe length of a string like this (varchar or char doesn't
>matter)
> 'AB12345678A '. The length is 14, but the function len('AB12345678A ')
> return 11. Len doesn't count the blank spaces. How con i get yhe length of
> my
> strings including blank spaces?
Try DATALENGTH instead.
SELECT LEN('AB12345678A ') returns 11
SELECT DATALENGTH('AB12345678A ') returns 14
Dan