Length of a string
-
Hi,
I am using a query :
select len(' ')
string contains blank spaces but it returns 0 length to me.
Have you any idea to correct this query ?
Regards,
Amit Gupta
-
It's how len() works with spaces. You can use datalength() instead for your purpose.
select len(' '), datalength(' ')
----------- -----------
0 6
(1 row(s) affected)
/Kenneth
-
Kenneth...
Thank you so much..
-
No offense, but it is how SQL treats trailing spaces, not leading spaces.
select len(' g'),
datalength(' g'),
len('G '),
datalength('G ')
-- -- -- --
6 6 1 6
N 56°04'39.16"
E 12°55'05.25" -
And you have to divide the datalength() output by 2 for unicode (nchar etc.) strings.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
You're correct Peter.
However, the question seemed to be how to count how many spaces there was in a space-only string.
The notion of dividing by two if there's a unicode string is a good one. Didn't think of that
/Kenneth
-
if you don't want the worry of Datalength and remembering the the division by 2 for NVarchars, but you want an accurate length that includes trailing spaces, why not simply use the replace function to change the spaces to something else? Then, if the variable gets changed to something else like a varchar, and you forget the division by 2 you don't hit errors.
Declare @stringWithTrailingBlanks nvarchar(255)
Select @stringWithTrailingBlanks=' '
select len(replace(@stringWithTrailingBlanks,' ','|'))
-
-
Nice! Let's only hope that the first sequence of spaces does not equal 8000 spaces. Because that will produce the result of -1.
N 56°04'39.16"
E 12°55'05.25" -
And divide that by 2 for unicode strings!
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
I thought you only had to divide by 2 when using datalength.
-
Yes, sorry, not clear. I was referring to the maximum capacity of 8000, as mentioned in the post above.
The point being that insofar as you need to avoid overflow, you still have to shoulder the (supposedly very onerous!) burden of remembering what kind of data you are using.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply