January 21, 2009 at 3:47 am
Does anyone know why LEN excludes any trailing spaces - surely if you wanted to ignore them you'd trim the value first ! :ermm:
January 21, 2009 at 3:55 am
I think this is to work with char fields. Probably left over behavior from the past.
January 21, 2009 at 3:57 am
I just wasted loads of time trying to work out what was wrong with my code :crazy:
January 21, 2009 at 7:29 am
The ANSI standard for variable length character fields indicates that trailing spaces pretty much don't exist. This is also why they do not matter when you join from one table to another.
Of course, apparently only IBM can remove both leading and trailing spaces now:
January 21, 2009 at 8:00 am
Hmm I wonder why IBM are struggling 😀
January 22, 2009 at 7:06 am
You can use DataLength to get the "real" size including trailing blanks.
Declare @strings varchar(30)
set @strings = 'ABC '
select Len(@strings) length, DataLength(@strings) datalength
Toni
January 22, 2009 at 7:16 am
Unless of course it's a char field in which case it would just return the length of the field that was declared
January 22, 2009 at 8:20 am
True. If it was just a CHAR field DATALENGTH would give the declared size and LEN would give the size up to any trailing blanks.
Though if it is meaningful for you to track the number of trailing blanks you assigned to a variable, I would think you would want to use VARCHAR. Am I missing something?
Toni
January 22, 2009 at 8:24 am
Andrew Reilly (1/22/2009)
Unless of course it's a char field in which case it would just return the length of the field that was declared
I'm assuming you're now talking about datalength....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 22, 2009 at 8:30 am
I was using a delimited string that i was splitting up and then using len to work out how many characters to strip out next time around, but when someone put a space at the end I started the next block of text with my delimiter. I got around this by using datalength, i could also just use charindex again to find where to start the next block.
Hope that made sense
January 22, 2009 at 11:02 am
It makes perfect sense. Glad you got it worked out.
Of course you could also use Jeff Moden's tally table method to pull out values from delimited strings or using Pattern index vs charindex allows you to use wildcards in the search so you could also do this:
declare @string varchar(100), @endofit int
set @string = ' abc ,de'
SELECT @endofit = case
when (PATINDEX('%,%', @string) > 0)
then (PATINDEX('%,%', @string))
else datalength(@string)+1
end
Select 'For string:('+@string+') - Next delimiter ends at:' + cast (@endofit-1 as varchar(2))
set @string = 'def '
SELECT @endofit = case
when (PATINDEX('%,%', @string) > 0)
then (PATINDEX('%,%', @string))
else datalength(@string)+1
end
Select 'For string:('+@string+') - Next delimiter ends at:' + cast (@endofit-1 as varchar(2))
Toni
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply