Get length of a VARCHAR in T-SQL 2

[jonbatts]

I have a table "MyTable", with 1 field "MyField" of type VARCHAR(27). Say I've forgotten the length of MyField and I want to use T-SQL to get it. I want "SELECT XXXX FROM MyTable" to return one record with one field, 27. What does "XXXX" need to be? Thanks, and have a great day.

 

[gmmastros]

To get the maximum size of the field (how much it can store)....

Select * 
From   Information_Schema.Columns
Where  Table_Name = 'YourTableName'
       And Column_Name = 'YourColumnName'

Look at the Character_Maximum_Length column.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ShankarJ]

Hi!

Do a SELECT * FROM INFORMATION_SCHEMA.COLUMNS first to view the information received and then choose the columns and selection criteria accordingly.

Regards

 

[earthandfire]

George, surely the COL_LENGTH function is easier:

[tt]SELECT COL_LENGTH('TableName', 'ColumnName')[/tt]

NB the quote symbols are required as COL_LENGTH takes nvarchars as its parameters.


Hope this helps.

 

[gmmastros]

earthandfire,

A star to you (because I didn't know about the col_length function).

In my code, I would prefer to use the information_schema method because it returns more information than just the column length. This can also be mis-leading, especially with nvarchar. Look at this...

Create Table TextNVarchar(Data nVarChar(100))

Select col_length('testnvarchar', 'data')

Select Character_Maximum_Length
From   Information_Schema.columns
Where  Table_Name = 'testnvarchar'
       And column_Name = 'data'

Drop Table TestNVarchar

The col_length function returns the storage space required for the field. Since it's nvarchar, which uses 2 bytes to store unicode data, you will get 200. The character_maximum_length will return 100.

So, I suppose it depends what you are trying to find out. If you want to set the max length on an input field, you should use the information_schema method. If you want to determine the storage requirements, then it is better to use the col_length function.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

and don't forget to use COLUMNPROPERTY for additional stuff, like if it is an identity etc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[earthandfire]

Thanks, George. I came across the META DATA Function list in BOL a few months ago when I needed some column information and had forgotten about the Information_Schema tables.

There a quite a few potentially useful functions there.

Lookup COL_LENGTH in BOL and click the link at the bottom to Metadata Functions.


Hope this helps.

 

[jonbatts]

COL_LENGTH seems to do what I need. I'm a .NET developer and my main interest is dynamically setting the max length of textboxes. Clients will say "Yes, the textbox for the Description of how they removed their underwear without taking off their pants should only need to be 200 characters", and then the next day say "No, we only want to allow them 100 characters to describe that." If I don't set the max length dynamically, obviously I'm having to go back and change the code, instead of just changing the database. Thanks for the help.

 

[gmmastros]

jonbatts,

It only seems to do what you want because you are using a varchar field. Next week, your boss might say... we need to support multiple languages. This would force you to change the field to nvarchar. Then, you will get the wrong value back. Please read carfully my explanation in the post with the timestamp of (10 Feb 07 10:05 ).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jonbatts]

Point taken. Thanks, George.