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
I thinks Datalength return number of bytes not the number of characters. If you have only spaces in the text field then how to check that field is empty.
Good tips!!!
Thanks bro
Awesome! Looking forward for such a feature to be made available in my favorite programming language. I’ve been dying to us “length” for short arrays and “datalength” for long arrays.
Thanks a lot. This was really handy
How can i get the one record size in a table? Can anyone help me on this
Thanks a lot
Very good! It’s work!
How add a where condition, example, WHERE fieldlength < 11 ?
Tanks
thanks, it was helpful
Tapan, I am glad that it helped you!
great thank you Pinal
HELLO ALL,
I WANT TO SHIFT MY DATA FROM ONE COLUMN TO NEXT COLUMN IF IT EXCEED 35 CHAR.I MEAN ONLY WANT TO SHIFT WHICH IS BEYOND 35 CHAR ..
EG.
MY NAME IS DISHA SHARMA FROM BANGALORE,KARNATAKA
IF I SET THE VALUE ONLY FOR SHIFT THE DAY IF IT EXCEED FIXED LENGTH THAT THERE IS NO MEANING OF THAT DATA
IT MENA IT CAN SHIFT THE CHAR FROM 36
LIKE ”ANGALORE,KARNATAKA IS SHIFTED TO NEXT COLUMN AN B IS IN 1ST COLUMN” SO CAN ANYONE HELP ME TO FIND IF IT EXCEDD 35 THEN HOW CAN I SHIFT MY DATA WITH MEANING IN SQL OR MS ACCESES
Hello Disha,
you could update the columns using substrings:
— test table with an ID, a long text field with the original data and the two “split fields”
CREATE TABLE MyTable ( id INT, sourceCol NVARCHAR(max), col1 NVARCHAR(35), col2 NVARCHAR(max))
— Some example records
INSERT INTO MyTable (id, sourceCol)
VALUES (1, ‘MY NAME IS DISHA SHARMA FROM BANGALORE, KARNATAKA’),
(2, ‘SHORT TEXT’);
— Look at the results first
SELECT *, SUBSTRING(sourceCol, 1, 35), SUBSTRING(sourceCol, 36, 1000) FROM MyTable
— now fill in the first 35 characters to col1 and all the rest to col2
UPDATE MyTable
SET col1=SUBSTRING(sourceCol, 1, 35),
col2=SUBSTRING(sourceCol, 36, 1000)
— look at the final result
SELECT * FROM MyTable