Click here to Skip to main content
15,936,709 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Everyone,

I am in doubt about what is the best form to obtain the length of an integer in a tsql query.
The logic seems to work when I do:
//Where @nbr_containers40 is an integer
LEN(@nbr_containers40)

But from what I've read, this function is meant to work with strings only and this made me worry about possible problems there might come from doing it.

So here is my question, can I keep as it is or should I change to something like:
LEN(CAST(@nbr_containers40 AS varchar(5))
or
LEN(CONVERT(varchar(5), @nbr_containers40))
Posted
Updated 8-Oct-13 20:28pm
v2
Comments
Mike Meinz 8-Oct-13 10:49am    
Why do you need to do this in a TSQL query? It would be far easier, to let your application program do formatting of the data.

If you must, use:
LEN(CONVERT(varchar(5), @nbr_containers40))
Paulo Augusto Kunzel 8-Oct-13 10:54am    
It is for a SSRS report and the recommended/requested is to get as much done on the sql side as possible.
Mike Meinz 8-Oct-13 10:56am    
Use CONVERT, if you must. I would have passed it as an integer so the report could do summing or other calculations if it needed to do so.
Paulo Augusto Kunzel 8-Oct-13 10:59am    
CONVERT is fine.
I know what you mean, but for the purpose of what I have to do it can't happen.

Thank you
Padam Agrawal 8-Oct-13 10:58am    
Yes, it is working with numeric variable too. but safer side you should use casting.

Cast is ANSI, convert is not.
ANSI-SQL is more portable across database platforms. So if you ever wanted to use the query on Orcale or others.

Thanks,

1 solution

Thing here depends on number you are going to give

See the examples below

Example 1:

SQL
DECLARE @List Int

SET @List = 11115

SELECT @List as list, CAST(@List AS varchar(5)) as caststring,CONVERT(varchar(5), @List) as convertstring

SELECT len(@List) as directlen,LEN(CAST(@List AS varchar(5))) as cst,LEN(CONVERT(varchar(5), @List)) as conv


VB
Result

list	caststring	convertstring
11115	11115	11115

directlen	cst	conv
5	5	5


Example 2:

SQL
DECLARE @List Int

SET @List = 111115

SELECT @List as list, CAST(@List AS varchar(5)) as caststring,CONVERT(varchar(5), @List) as convertstring

SELECT len(@List) as directlen,LEN(CAST(@List AS varchar(5))) as cst,LEN(CONVERT(varchar(5), @List)) as conv



VB
Result

list    caststring  convertstring
11115   11115   11115

directlen   cst conv
5   5   5



Two examples shows, if you are declaring convert() or cast() with varchar it will show upto data lenth declare only. If int has one character more than declare data limit they will not show it. If you your variable has certain limit you can go with convert().

Len() function it self convert as string measures the length of the integer variable.

Even you want to handle it see below example

SQL
DECLARE @List Int

SET @List = 1111115

SELECT @List as list, CAST(@List AS varchar) as caststring,CONVERT(varchar, @List) as convertstring

SELECT len(@List) as directlen,LEN(CAST(@List AS varchar)) as cst,LEN(CONVERT(varchar, @List)) as conv



VB
Result

list	caststring	convertstring
1111115	1111115	1111115

directlen	cst	conv
7	7	7
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900