Is it possible to get the max length of a TEXT field?

I have a text field and want to know if any of the text exceeds 10,000 characters
I can do a select max(len(rtrim(convert(varchar(8000)))) on the field but I'm not able to do for more than 8000 and you can't manipulate TEXT datay type.
Any ideas?
Thanks!SELECT DATALENGTH(Col1) FROM myTable99

Is it possible to create the columns of a #Temp table base on a query result?

I’ve been trying without success something like this…

CREATE TABLE #table

(

(SELECT MAX(Column) FROM Table) varchar(50)

)

I'm working with SQL 2005; thank you for any help in advance.

Not quite like that.

You can SELECT ... INTO.

In the process, you need to provide a column Name for any computed or derived columns, and you can change the datatype.

Something like this:

Code Snippet

USE Northwind
GO

SELECT cast( max( EmployeeID ) AS decimal(6,2)) AS MaxEmp
INTO #MyTable
FROM Employees

|||

There are 2 options,

Create Table #Table (

ColumnValue Varchar(50)

);

Insert Into #Table

SELECT MAX(Column) FROM TableName;

--OR

SELECT Cast(MAX(Column) as Varchar(50)) ColumnValue Into #Table FROM TableName;

|||Thank you for the help provided so far. Another question on the same subject. How can the Column name be assigned dynamically after a query result? Thanks.
|||

You should use the aliase name. if you failed to give the aliase name for the expression sql server thow an error says that "No column was specified for column n on tablename".

Code Snippet

Select Max(Column) as MaxColumn Into NewTable From OldTable

Select Max(Column) MaxColumn into Newtable From OldTable

|||

As indicated above, No.

My apologies, I guess this statement was not clear enough.

In the process, you need to provide a column Name for any computed or derived columns...

sql

is it possible to change the Datatype in a excel file from the Float to varchar

Hi,

I have a excel file and i am trying to import zip codes to the database... but the some of the zip codes start with 06902 but the excel file treats them as float but i want to treat them as varchar...

How can i do it.

Regards

Karen

Try changing the column in Excel to a Text Column. To do that, select the column by clicking on the column heading, then format the cells and select text.|||

Thanks for ur answer,

I did it... But when i transfer the data to sql and the column which were converted to Text are NULL in Sql server...

so what should i do... and when i create a table it takes it RtnAddrZip as Float in excel

REgards

Karen

is it posible to put more than 4000 bytes into one column? ( sql server mobile )

varchar can only hold 4000 bytes
and there is no text column in sql server mobile

Hi,
try 'ntext'

Pete

|||I'm using SQL CE 3.5 with VS 2008 Beta 2:

I have a table with some coulmns of NTEXT type, but when i want to update my dataset to database with a row which has that field more than 4000 charachters I got this error:

"InvalidOperationException was unhandled
@.p4 : String truncation: max=4000, len=4374
...."

Regards,
Parham.
|||NTEXT should accept 536870911 charachters! But why iam getting that Error?!
|||

It is probably a problem with the DataSet designer. Check the designer generated code, and you may find that it has limited the @.p4 length to 4000. You can probably manually change this.

|||I'm having the same issue. I checked through the designer code and found the max length set to the correct length for ntext (536870911). Just to be sure I recreated the database and the dataset, but got the same result. I think this might be a genuine bug.

is it posible to put more than 4000 bytes into one column? ( sql server mobile )

varchar can only hold 4000 bytes
and there is no text column in sql server mobile

Hi,
try 'ntext'

Pete

|||I'm using SQL CE 3.5 with VS 2008 Beta 2:

I have a table with some coulmns of NTEXT type, but when i want to update my dataset to database with a row which has that field more than 4000 charachters I got this error:

"InvalidOperationException was unhandled
@.p4 : String truncation: max=4000, len=4374
...."

Regards,
Parham.
|||NTEXT should accept 536870911 charachters! But why iam getting that Error?!
|||

It is probably a problem with the DataSet designer. Check the designer generated code, and you may find that it has limited the @.p4 length to 4000. You can probably manually change this.

|||I'm having the same issue. I checked through the designer code and found the max length set to the correct length for ntext (536870911). Just to be sure I recreated the database and the dataset, but got the same result. I think this might be a genuine bug.

is it posible to put more than 4000 bytes into one column? ( sql server mobile )

varchar can only hold 4000 bytes
and there is no text column in sql server mobile

Hi,
try 'ntext'

Pete

|||I'm using SQL CE 3.5 with VS 2008 Beta 2:

I have a table with some coulmns of NTEXT type, but when i want to update my dataset to database with a row which has that field more than 4000 charachters I got this error:

"InvalidOperationException was unhandled
@.p4 : String truncation: max=4000, len=4374
...."

Regards,
Parham.
|||NTEXT should accept 536870911 charachters! But why iam getting that Error?!
|||

It is probably a problem with the DataSet designer. Check the designer generated code, and you may find that it has limited the @.p4 length to 4000. You can probably manually change this.

|||I'm having the same issue. I checked through the designer code and found the max length set to the correct length for ntext (536870911). Just to be sure I recreated the database and the dataset, but got the same result. I think this might be a genuine bug.

is it posible to put more than 4000 bytes into one column? ( sql server mobile )

varchar can only hold 4000 bytes
and there is no text column in sql server mobile

Hi,
try 'ntext'

Pete