How to know the length of data stroed in table column?

Question

Post reply

How to know the length of data stroed in table column?

ruchi-761283

SSC-Addicted

Points: 434
More actions
March 17, 2010 at 10:28 pm

#138879

Hi All,
I want to know the length of data stored in a table column.
Is there any way to determine this?
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

SELECT LEN('HOW LONG IS THIS DATA?')

RESULT = 22...

what you looking for?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

You could try this other methods too

select charindex(Right('HOW LONG IS THIS DATA?',1),'HOW LONG IS THIS DATA?')+1-charindex(left('HOW LONG IS THIS DATA?',1),'HOW LONG IS THIS DATA?')

select charindex(Right('HOW LONG IS THIS DATA?',1),'HOW LONG IS THIS DATA?')

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 3

ruchi-761283 (3/17/2010)
I want to know the length of data stored in a table column.

If you are looking for the maximum length of data (in bytes) that the data type of the column permits, look at the max_length column on the sys.columns system view.

If you want the number of bytes used to store a particular value in a column, use the DATALENGTH function. This works with any expression, not just column values.

Notice that the LEN function only works with string expressions, and ignores trailing spaces.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Paul White (3/18/2010)
ruchi-761283 (3/17/2010)
I want to know the length of data stored in a table column.
If you are looking for the maximum length of data (in bytes) that the data type of the column permits, look at the max_length column on the sys.columns system view.
If you want the number of bytes used to store a particular value in a column, use the DATALENGTH function. This works with any expression, not just column values.
Notice that the LEN function only works with string expressions, and ignores trailing spaces.

Thanks for bringing up sys.columns and DataLength.

Nice way of giving away all of the answers in one reply too. Covered the bases.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 5

CirquedeSQLeil (3/18/2010)
Nice way of giving away all of the answers in one reply too. Covered the bases.

Ha. He'll just come back and say he was after the sum of the lengths in the column, or something 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Paul White NZ (3/18/2010)
CirquedeSQLeil (3/18/2010)
Nice way of giving away all of the answers in one reply too. Covered the bases.
Ha. He'll just come back and say he was after the sum of the lengths in the column, or something 😉

I'm sure you could provide a more elaborate script for that:-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events