How to get the length of an integer on with TSQL

Question

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 8-Oct-13 4:42am

Paulo Augusto Kunzel

Updated 8-Oct-13 20:28pm

coded007

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

coded007 · Accepted Answer · 2013-10-08T20:42:00

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