March 25, 2008 at 11:28 am
I have the following query...
select len(name)as Length, right(name, (len(name))) as RightNameFullLength,left(name, (len(name))) as LeftNameFullLength ,len(right(name, len(name))) as RightChars,len(left(name, (len(name)))) as LeftChars, name
from syscolumns where id = object_id('cblslon0') and name like '%total%'
Both the fields are 16 characters in length, yet when i run the query above i get the following output...
16LON0_TOTAL_FEES SLON0_TOTAL_FEES1516SLON0_TOTAL_FEES
16LON0_TOTAL_LOAN SLON0_TOTAL_LOAN1516SLON0_TOTAL_LOAN
So, it knows the length is 16, but when i do right() of the full legnth it returns one less character than it should!! left works fine. This isnt on all columns in the table, just on 3 or 4 out of 200ish. I am just completely baffled by it as there is no logical explanation of what it is doing. Just wondering if anyone has had any similar experiances?
ps, i have run checktable on the syscolumns and cblslon0 tables. both are fine.
I cannot replicate the problem either, i used the following script to try but the results were as expected...
use tempdb
create table myTable
(
SLON0_TOTAL_FEES varchar(10)
)
select len(name)as Length, right(name, (len(name))),left(name, (len(name))),len(right(name, len(name))) as RightChars,len(left(name, (len(name)))) as LeftChars, name
from syscolumns where id = object_id('myTable')
drop table myTable
16SLON0_TOTAL_FEESSLON0_TOTAL_FEES1616SLON0_TOTAL_FEES
March 25, 2008 at 1:47 pm
Len does not count blank spaces. Make sure you are trimming your string. This is your poblem in a nutshell.
declare @var varchar(20)
set @var = 'adam haines '
select right(@var,len(@var))
To remedy the problem use RTRIM and LTRIM respectively.
declare @var varchar(20)
set @var = 'adam haines '
select right(RTRIM(@var),len(@var))
March 26, 2008 at 2:32 am
Cheers for the reply Adam, however it isnt the answer. The column has no spaces in it SLON0_TOTAL_FEES There are actually 16 characters, the legnth knows there are 16 chars but when i do right(col, len(col)) i get 15.
Its odd.
March 27, 2008 at 4:09 am
Are you sure they're all ansi chars?
Right() might count bytes or chars from right.
March 27, 2008 at 7:17 am
There has to be some outstanding variables here. Compare the problematic column datatypes to working datatypes. You can also see if using substring returns the same results.
SELECT SUBSTRING ( expression ,start , length ),
LEN(SUBSTRING ( expression ,start , length ))
March 27, 2008 at 7:47 am
Adam Haines (3/27/2008)
There has to be some outstanding variables here. Compare the problematic column datatypes to working datatypes. You can also see if using substring returns the same results.
SELECT SUBSTRING ( expression ,start , length ),
LEN(SUBSTRING ( expression ,start , length ))
Adam, the query above gives me the correct character length of 16.
SLON0_TOTAL_FEES16
SLON0_TOTAL_LOAN16
The exact same query (from my origional post) ran in the live environment with no problems and the right(len) returned all 16 characters! Its really odd, i cant see me getting to the bottom of this one.
March 27, 2008 at 9:16 am
The datalength() function may help dx. Len() rtrims before it computes; datalength() doesn't.
Do note how datalength() returns the true length of unicodes:
declare @k varchar(10), @nk nvarchar(10)
select @k = '123456789 ', @nk = '123456789 '
select len(@k), datalength(@k), len(@nk), datalength(@nk)
----------- ----------- ----------- -----------
9 10 9 20
May need to look at your data with a hex editor to see what's really there. I like Notepad++; works well enough & freeware.
March 27, 2008 at 9:34 am
jkinlaw (3/27/2008)
The datalength() function may help dx. Len() rtrims before it computes; datalength() doesn't.Do note how datalength() returns the true length of unicodes:
declare @k varchar(10), @nk nvarchar(10)
select @k = '123456789 ', @nk = '123456789 '
select len(@k), datalength(@k), len(@nk), datalength(@nk)
----------- ----------- ----------- -----------
9 10 9 20
May need to look at your data with a hex editor to see what's really there. I like Notepad++; works well enough & freeware.
Hmm, this does make for interesting results...
select datalength(name),len(name)as Length, right(name, (datalength(name))) as RightNameWithDataLength ,
right(name, (len(name))) as RightNameFullLength,left(name, (len(name))) as LeftNameFullLength
,len(right(name, len(name))) as RightChars,len(left(name, (len(name)))) as LeftChars, name
from syscolumns where id = object_id('cblslon0') and name like '%total%'
3416SLON0_TOTAL_FEES LON0_TOTAL_FEES SLON0_TOTAL_FEES1516SLON0_TOTAL_FEES
3416SLON0_TOTAL_LOAN LON0_TOTAL_LOAN SLON0_TOTAL_LOAN1516SLON0_TOTAL_LOAN
So, the datalength comes back as a length of 34??, the data type of name in syscolumns is sysname (varchar 256). when i do the right(datalength()) it comes back correct! Im not sure 100% what this tells me about using right(len()) tho 😛
March 27, 2008 at 9:56 am
The real question here is what is your goal. You are using Right but the net affect is the same as using LTRIM
LTRIM(ColName),
With LEN it does not count trailing blanks.
From Books Only
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
DATALENGTH counts all
From Books Only
Returns the number of bytes used to represent any expression.
And when using RIGHT the point it starts at is the very end of the expression and you value is that many characters from the true end.
So this is what you get base on Val as example.
[Code]
-----Ex 1
Val = 'Adam'
LEN = 4
DATALENGTH = 4
RIGHT(Val,LEN(Val)) = 'Adam'
RIGHT(Val,DATALENGTH(Val)) = 'Adam'
-----Ex 2
Val = 'Adam '
LEN = 4
DATALENGTH = 5
RIGHT(Val,LEN(Val)) = 'dam '
RIGHT(Val,DATALENGTH(Val)) = 'Adam '
-----Ex 3
Val = ' Adam'
LEN = 5
DATALENGTH = 5
RIGHT(Val,LEN(Val)) = ' Adam'
RIGHT(Val,DATALENGTH(Val)) = ' Adam'
-----Ex 4
Val = ' Adam '
LEN = 5
DATALENGTH = 6
RIGHT(Val,LEN(Val)) = 'Adam '
RIGHT(Val,DATALENGTH(Val)) = ' Adam '
-----Ex 5
Val = ' Adam '
LEN = 5
DATALENGTH = 7
RIGHT(Val,LEN(Val)) = 'dam '
RIGHT(Val,DATALENGTH(Val)) = ' Adam '
[/code]
March 27, 2008 at 10:09 am
Antares, i didnt really have a goal as such, it was just something i was messing about with and i noticed it gave the "wrong" result, hence the post.
what i was actually trying to do was look at what columns existed in one table that wernt in another and all the columns in table1 start with slon0_XXXXXXXX and all the ones in Table2 are ulon0_XXXXXXXX. so to see which columns were different i was doing the join on right(column, len(column)-5) and thats when I noticed that when doing the right(len) thing it thought the length was 17 for some reason therefore missing off the first letter on the result set.
It was just really puzzling and as stated only happened on a couple of fields out of 200.
What puzzles me more is that the datalength() comes back as 34, so after reading your post you would expect select right(column, len(column)) for the column name of SLON0_TOTAL_FEES (16 chars) to come back with blanks (34-16 is 18, so the right 18 would be blank!) but its not blank, it gives LON0_TOTAL_FEES 🙂
this is confusing me just writing this stuff 😛
March 27, 2008 at 10:55 am
column name is nvarchar, not varchar. nvarchar is unicode. UTF8 can have 1, 2 or 3 bytes per character, where lead byte determines how to decode other bytes. So, right() would have to scan from start for proper result. UTF16 uses fixed 2 byte char encoding, guess which is used by sqlserver.
For this reason I asked if you're sure all chars in column name are ansi.
datalength() returns bytes, len() returns chars without trailing whitespace.
March 27, 2008 at 10:55 am
declare @x sysname
set @x = 'my_mother_told_me '
select datalength(@x), len(@x), right(@x,len(@X))
Actually that is easy. You stated the following in a prior post
the data type of name in syscolumns is sysname (varchar 256).
But that is incorrect, the datatype sysname is equal to nvarchar(256) not varchar(256).
When datalength counts it counts the unicode characters as 2 bytes each.
Thus 'A' in varchar yields 1 to datalength but 'A' in nvarchar yields 2 because it counts bytes and not characters and each character in nvarchar is 2 bytes.
As well, LEN will return 1 either way with 'A' because as stated in my prior post
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
RIGHT also manages by character (actually converts implicitly the data to varchar) and not byte.
From BOL
Return Types
varchar
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.
So when you say DATALENGTH return 34 it actually measured 17 characters based on your datatype (sysname). This is the reason for your variance.
And sorry my examples were bad becuase I failed to note I vbased on varchar.
If was based on NVARCHAR it would be the same but here is where things would be messed up with DATALENGTH so be carefull.
Ex.
DECLARE @val NVARCHAR(4)
SET @val = 'Adam'
SELECT Len(@Val)
SELECT Datalength(@Val)
SELECT Right('Tack on Front' + @val, Len(@Val))
SELECt Right('Tack on Front' + @val, Datalength(@Val))
---Details
Val = 'Adam' (nvarchar(4))
Len(Val) = 4
Datalength(Val) = 8
Right('Tack on Front' + Val, Len(Val)) = 'Adam' --Single quotes not part of output, there to show actual end points.
Right('Tack on Front' + Val, Datalength(Val)) = 'rontAdam' --Demonstraights issue cause by concatination with nvarchar and datalength.
[/code]
March 27, 2008 at 11:04 am
fantastic, got it!! i now see where the 34 comes from 🙂 with this in mind i checked that right(ltrim(rtrim(name)), (len(name))) gave me the full column name, and it does!! Robert, sorry for not getting back to your post earlier and thanks for your input on this topic.
Its been quite educational really, a useful insight into the use of len, right, datalegnth and unicode.
Thanks all
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply