problem with function LEN()

Question

Post reply

problem with function LEN()

gorenak

Old Hand

Points: 394
More actions
July 22, 2010 at 2:07 am

#235532

Why
select LEN(SUBSTRING('VOLTAFLEX/VOLTAREN EMULGEL IZTISKALEC TUBE', 1, 35))
select LEN(SUBSTRING('VOLTAFLEX GLUCOSAMINE 625MG 60TBL. SI', 1, 35))
this return different number 34 and 35 in MSSQL 2008?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Have a look inn BOL at the definition for the LEN function... it states that it:-

Returns the number of characters of the specified string expression, excluding trailing blanks.

The trailing blank for you second SELECT is being dropped.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

gorenak (7/22/2010)
Why
select LEN(SUBSTRING('VOLTAFLEX/VOLTAREN EMULGEL IZTISKALEC TUBE', 1, 35))
select LEN(SUBSTRING('VOLTAFLEX GLUCOSAMINE 625MG 60TBL. SI', 1, 35))
this return different number 34 and 35 in MSSQL 2008?

This is due to the 35th character in the second string which happens to be "SPACE".. LEN function will not calculate the trailing spaces.. if u want to get the full accurate lenght , accurate here means inclusion of trailing spaces, use DATALENGTH funtion..

Like

select DATALENGTH(SUBSTRING('VOLTAFLEX/VOLTAREN EMULGEL IZTISKALEC TUBE', 1, 35))

select DATALENGTH(SUBSTRING('VOLTAFLEX GLUCOSAMINE 625MG 60TBL. SI', 1, 35))

Hope this clears the air..

gorenak Old Hand Points: 394 More actions · Answer 3

But I have also some more questions.

select DATALENGTH(SUBSTRING('URGO OBLIŽ ZA RANE IN ODRGNINE, 5 OBLIŽEV', 1, 35))

this return 35 OK

declare @txt_value nvarchar(100)

set @txt_value = 'URGO OBLIŽ ZA RANE IN ODRGNINE, 5 OBLIŽEV'

select DATALENGTH(SUBSTRING(@txt_value, 1, 35)) -- return 70 ????

select len(SUBSTRING(@txt_value, 1, 35)) --- return 35 ok

I don`t understand why return 70 ?

thanks for help.

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

It returns 70 because of this... declare @txt_value nvarchar(100)

You have declared your variable as nvarchar, which is unicode, and uses up 2 bytes of storage for every character in the string.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

due to the fact that u used NVARCHAR... NVARCHAR is double length as that of VARCHAR... try changing it to VARCHAR and u will get ur result..