To measure the length of VARCHAR fields the function LEN(varcharfield) is useful.
To measure the length of TEXT fields the function is DATALENGTH(textfield). Len will not work for text field.
Example:
SELECT DATALENGTH(yourtextfield) AS TEXTFieldSize
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
84 Comments. Leave new
Hello!
But, when the field is null ?
how can i know the size of a field, have or not have data in the table ??
Thanks!
When the value is NULL, the size is also NULL
Good work !!
Good tips!!!
Thanks bro
can anybody help me in using length function
how i can make any field have 4 digits (e.g 7456)
to 6 digits(i.e 007456) in my data base using sql query so that every field with length 4 is changed to length 6
but zero should come in front
It is formation issue. If you use front end application do the formation there
any one there
plz reply
@ran
Is this a VARCHAR field?
UPDATE tab SET col = ’00’ + col WHERE LEN(col) = 4;
@ran
UPDATE tab SET col = RIGHT(‘000000’ + col, 6)
Really great tip.
Thanks mate.
Hi! This is not the first time your posts help me. Thank you so much!
Thanks a lot for such a useful tip.
Please help.
Need to add/combine 3 columns
Column1 column column3
eddie lewis 125.87456
query is select :
column1+column2+cast(column3/100 as varchar(20))
returns – eddielewis1.25874 i.e missing last 2 digits of column3 …… please help
Thanks
Hey Andazi,
Change the query to
column1+column2+cast(column3/100 as varchar(50))
As the cast is only getting the first 20 characters
THIS HELPED ME SO MUCH – THANK YOU!!!
You guys explained how to change the data amount being returned directly in SQL server, but what about the amount being returned in an ASP page?
This is great!
But how can I find out the MAXSIZE of the field in question?
We have recently updated our DB to increase the size of certain fields. I need to check if the user updated their DB before performing any work on those fields.
Hello,
I have a table which has field1, field2 and field3 columns of type int, the combination of those columns represent 10 digit phone number. I know field3 always have 4 digits, but MS SQL trims the right most 0s. all i am tring to acheive is to provide the user with one field 10 digit number.
here is my query:
INSERT INTO [Info Direct Res new]
SELECT ‘Info Direct’, /* Datbase Name */
DECLARE @MAX_FIELD_LEN int;
DECLARE @fieldLength int;
SET @MAX_FIELD_LEN=4;
SET @fieldLength = len(Cast(field3 as varchar));
cast(field1 as varchar)+cast(field2 as varchar)+RIGHT(‘0’,@MAX_FIELD_LEN-@fieldLength)+Cast(field3 as varchar), /* Phone Number */
field5, /*First name*/
field6, /*Last Name*/
ISNULL(field8,”) + ‘ ‘ + ISNULL(field9,”)+ ‘ ‘ + ISNULL(field10,”)+ ‘ ‘ + ISNULL(field11,”)+ ‘ ‘ + ISNULL(field12,”), /* Full Address */
field13, /* city */
field14, /* province */
field15, /* postal code */
‘ ‘
from stcir1001
I get syntax error where i say declare
thanks,
Just what I needed, thank you
I have a tabe in oracle which has a varchar field with length of 48. I need to select records that are not in the length of either 8, 15 & 16 size.
The second criteria is to identify records which are not numeric i.e. any character value should be displayed.
Thanks Alot.
I got what i wanted since many days.
I was trying LENGTH bat it wasnt working.
Thanks alot once again.
My SQL related googling always leads me to your blog, and your articles are usually pretty helpful. Thanks!
The msdn website shows that len and datalength functions are different.
LEN- Returns the number of characters of the specified string expression, excluding trailing blanks.
DATALENGTH – Returns the number of bytes used to represent any expression.