len() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

len()

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-27 : 11:53:18

i have a record which is blank, not NULL in a table.

when i use len(). It shows me 25. why?
isnt supposed to be 0?

Kristen
Test

22859 Posts

Posted - 2006-11-27 : 11:54:56

What's the syntax you are using?

"len() is supposed to count the number of strings in the record."

LEN() counts the number of characters in a string expression - excluding trailing spaces.

select len(convert(char(20), '')) -- 0
select datalength(convert(char(20), '')) -- 20

Kristen

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-27 : 12:02:26

You are using char instead of varchar. varchar does not pad with spaces, but char does. Run this code and you'll see the difference.

declare @vc varchar(25)
declare @c char(25)
select @vc = '', @c = ''
select len(@vc), len(@c)
select datalength(@vc), datalength(@c)

Kristen
Test

22859 Posts

Posted - 2006-11-27 : 12:58:17

"varchar does not pad with spaces, but char does."

Yeah, but LEN() doesn't count the trailing spaces ...


len(@vc)    len(@c)
----------- ----------- 
0           0

datalength(@vc) datalength(@c)
-----------     ----------- 
0               25

... need to see funketekun's example I think.

Kristen

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-27 : 13:23:14

krsiten,
the field is varchar(100).
the syntax is
select top 5 ad_str1, ascii(ad_str1) asciii, len(ad_str1) number_of_characters from trio where len(ad_str1) > 0 order by ad_str1

the result is
0 25
0 25
0 25
PAYSON ROAD 32 13
A STREET 32 10

i tested this in QA
select ascii(' ') = 32
select ascii('') = null

that means there is something in that blank field. but what it is that makes it 0?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 13:30:29

select top 5 ad_str1, ascii(ad_str1) asciii, len(ad_str1) number_of_characters from trio where len(rtrim(ad_str1)) > 0 order by ad_str1

Peter Larsson
Helsingborg, Sweden

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-27 : 13:32:54

peso,

i used this
select top 5 ad_str1, ascii(ad_str1) asciii, len(ad_str1) number_of_characters from trio where len(rtrim(ltrim((ad_str1)))) > 0 order by ad_str1

0 25
0 25
0 25 PAYSON ROAD
32 13 A STREET
32 10

same result. Any function to find out what is in the blank record. maybe its invicible.

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-27 : 13:41:07

ok i found out...
as i dumped this records to sqlServer from MSAccess.
this particular record is a standing rectangle. But after importing to sqlserver is blank. ITS WEIRD.

NOW how do i select the records that are not null, and only has strings >0 and i dont want that weird record to show.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 13:43:57

Got ASCII(160) / "hard space" involved?

Peter Larsson
Helsingborg, Sweden

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-27 : 13:57:40

peso ,

no ascii(160) or "hard space"
it only has a standing rectangle on the msAccess file. and it shows blank in QA.

Kristen
Test

22859 Posts

Posted - 2006-11-27 : 14:53:18

"select top 5 ... ascii(ad_str1) AS asciii ..."

If that gives a result of Zero then you have some binary/unicode characters is your data.

Kristen

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-11-28 : 14:10:04

funketekun,

quote:

it only has a standing rectangle on the msAccess file.

If you're actually seeing a rectangle in Access, that means the field isn't empty. You've got something in the field that isn't mapped to a character in the font being used by Access.

According to your select above using Ascii(), you're getting ASCII character 25. That's an EM (End of Medium) character, or hex character 0x19. You might be able to find those records by using CharIndex(Char(25), yourfield, 0) in your WHERE clause.

Ken

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-11-29 : 03:23:23

quote:
If you're actually seeing a rectangle in Access, that means the field isn't empty. You've got something in the field that isn't mapped to a character in the font being used by Access.

I agree with this, just because it looks blank doesn't mean it is - it depends entirely on the font and the ASCII code. For example ASCII code 13 is usually a non printing character but sometimes shows as a rectangle.

I come across this every now and again and if I want to know what the characters are I put the string into Excel and convert the individual characters to ASCII so I can see what it is against an ASCII table. Maybe not the best way to do it but it works for me.

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources