Len did not display right size

Question

Len did not display right size

adonetok

SSCrazy Eights

Points: 8395
More actions
December 21, 2012 at 2:13 pm

#267935

I use len function to find out varchar column data size but did not display right size.
For example, in some cell of varchar column, data is "0", "6"... but return are 11
select NDC, len(NDC) as NDC_LEN from tableA
RESULT:
NDC ---NDC_LEN
----------------
0------11
6------11
I use len(ltrim(rtrim(NDC))) but got the same result.
How to fix it?

Viewing 15 posts - 1 through 14 (of 14 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

adonetok (12/21/2012)
I use len function to find out varchar column data size but did not display right size.
For example, in some cell of varchar column, data is "0", "6"... but return are 11
select NDC, len(NDC) as NDC_LEN from tableA
RESULT:
NDC ---NDC_LEN
----------------
0------11
6------11
I use len(ltrim(rtrim(NDC))) but got the same result.
How to fix it?

You are going to have to provide some details before we can do much to help. ddl, sample data please. Without ddl at the very least we are left to guess what the datatypes are. Please see the article at the first link in my signature for how to post a question.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 2

adonetok (12/21/2012)
I use len function to find out varchar column data size but did not display right size.
For example, in some cell of varchar column, data is "0", "6"... but return are 11
select NDC, len(NDC) as NDC_LEN from tableA
RESULT:
NDC ---NDC_LEN
----------------
0------11
6------11
I use len(ltrim(rtrim(NDC))) but got the same result.
How to fix it?

What do you get for the following?

select NDC, len(NDC) as NDC_LEN, LEN(REPLACE(NDC,CHAR(160),CHAR(32))) As LenWithHardSpacesRemoved FROM TableA

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

adonetok SSCrazy Eights Points: 8395 More actions · Answer 3

The same result

NDCNDC_LEN LenWithHardSpacesRemoved

011 11

611 11

511 11

011 11

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

Again...what is the datatype? The best you are going to get is pure speculation unless you give us the whole picture.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

How about

SELECT '[' + NDC + ']' FROM TableA

?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

Try running this against your table and see what ASCII codes fall out:

DECLARE @T TABLE (MyChar VARCHAR(11))

INSERT INTO @T

SELECT '0' + CHAR(10) + CHAR(13) + CHAR(9)

UNION ALL SELECT '6' + REPLICATE(CHAR(10) + CHAR(13) + CHAR(9), 3)

UNION ALL SELECT '8 '

;WITH Tally AS (

SELECT n=number

FROM [master].dbo.spt_values Tally

WHERE [Type] = 'P' AND Number BETWEEN 1 AND 11),

Codes AS (

SELECT MyChar, [LEN(MyChar)]=LEN(MyChar), [DATALENGTH(MyCHAR)]=DATALENGTH(MyCHAR)

,n, [ASCII]=ASCII(SUBSTRING(MyChar, n, 1))

FROM @T

CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND LEN(MyChar)) a)

SELECT MyChar, [LEN(MyChar)], [DATALENGTH(MyCHAR)]

,[ASCII]=STUFF((

SELECT ',' + CAST([ASCII] AS VARCHAR(3))

FROM Codes b

WHERE a.MyChar = b.MyChar

ORDER BY n

FOR XML PATH('')), 1, 1, '')

FROM Codes a

GROUP BY MyChar, [LEN(MyChar)], [DATALENGTH(MyCHAR)]

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

ScottPletcher SSC Guru Points: 100946 More actions · Answer 7

It really looks like the column is "char(11)" and not "varchar(11)".

Can you verify the column's data type?

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 8

ScottPletcher (12/26/2012)
It really looks like the column is "char(11)" and not "varchar(11)".
Can you verify the column's data type?

Char(11) and Varchar(11) should make no difference when it comes to LEN. LEN ignores trailing spaces. There's something else in the data and the OP supposedly verified that its not the CHAR(160) character.

That, notwithstanding, I agree.... it would really be nice to know what the datatype of the column is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

adonetok SSCrazy Eights Points: 8395 More actions · Answer 9

Thank you for help.

Data type is varchar(11)

I did one test:

1) make duplicate blank table

2) insert data as '00012345678' and '000123'

3) run scripts for len and got result are 11 and 6

It tells me that problem is not table itself but it is coming from data.

Question: How to find out hidden gabage space from data?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 10

Would it be a problem with leading spaces (instead of trailing spaces)? Maybe hard spaces or tabs.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

adonetok SSCrazy Eights Points: 8395 More actions · Answer 11

I use len(rtrim(ltrim(NDC))) but got the same problem

Sean Lange SSC Guru Points: 286573 More actions · Answer 12

adonetok (1/2/2013)
I use len(rtrim(ltrim(NDC))) but got the same problem

Then you must have some non-display characters in your data. Try outputting select * to text instead of the grid. Do you have carriage returns? Export the data to word and show all the editing stuff. Do you see line breaks? If all else fails examine each and every character in the data and see what the ascii values are. There has to be something in there.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dwain Camps SSC Guru Points: 86908 More actions · Answer 13

Sean Lange (1/2/2013)
adonetok (1/2/2013)
I use len(rtrim(ltrim(NDC))) but got the same problem
Then you must have some non-display characters in your data. Try outputting select * to text instead of the grid. Do you have carriage returns? Export the data to word and show all the editing stuff. Do you see line breaks? If all else fails examine each and every character in the data and see what the ascii values are. There has to be something in there.

Did you try running the query I suggested? It should show definitively which characters are cluttering up your VARCHARs.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

vliet SSCommitted Points: 1998 More actions · Answer 14

Please notice that the other whitespace characters (TAB, CR, LF, with ASCII codes 9, 13, 10) do not show up in the output of most query tools. These whitespace characters are not removed by the trim functions so the length remains the same with or without a trim applied to the values. You can check whether this is the case with NDC LIKE '%[' + CHAR(9) + CHAR(13) + CHAR(10) + ']%'.