SQL SERVER – Find Length of Text Field

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)

,
Previous Post
SQLAuthority.com News – Journey to SQL Authority Milestone of SQL Server
Next Post
SQL SERVER – Retrieve Current DateTime in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

Related Posts

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.

    Reply
  • Good tips!!!
    Thanks bro

    Reply
  • 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.

    Reply
  • Thanks a lot. This was really handy

    Reply
  • How can i get the one record size in a table? Can anyone help me on this

    Reply
  • Thanks a lot

    Reply
  • Very good! It’s work!
    How add a where condition, example, WHERE fieldlength < 11 ?
    Tanks

    Reply
  • thanks, it was helpful

    Reply
  • great thank you Pinal

    Reply
  • DISHA SHARMA
    July 14, 2016 1:02 pm

    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

    Reply
  • Ralf Wohner, simple fact AG
    August 4, 2016 9:14 pm

    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

    Reply

Leave a Reply

Menu