Get longest length of data in a column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Get longest length of data in a column

Get longest length of data in a column

Rate Topic

Display Mode

Topic Options

Author

Message

Warren Peace

Posted Tuesday, October 30, 2007 5:51 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 06, 2012 9:48 AM
Points: 207, Visits: 450

I am just curious about some data. I have a column in my table and it is varchar(3000). How can I query that column for the longest value in there and give me a count of how long it is?

Assume table name is "LOGS" and the Column name is "Location"

Thanks

Post #416839

Dave I

Posted Tuesday, October 30, 2007 9:07 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 28, 2013 3:26 PM
Points: 114, Visits: 477

I'm a little rusty but how about something like this?

select top 1 with ties
   LogsID, len(Location), Location
from [LOGS]
order by len(Location) desc

Post #416862

Pam Brisjar

Posted Tuesday, October 30, 2007 9:44 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 18, 2013 5:52 AM
Points: 908, Visits: 2,797

select max(datalength(YourColumn)) from YourTable

Post #416865

Dave I

Posted Tuesday, October 30, 2007 10:48 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 28, 2013 3:26 PM
Points: 114, Visits: 477

I wanted to give him a little more information than that.
I figured he would be able to pull that one off by himself.

Post #416868

Warren Peace

Posted Friday, November 02, 2007 10:18 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 06, 2012 9:48 AM
Points: 207, Visits: 450

I got two different results. The first returned 75 and the other returned 150. I double checked it by writing a vbscript to get the count of each record length and 75 was correct.

Thanks everyone.

Post #418039

Gale

Posted Friday, August 19, 2011 8:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 03, 2012 10:13 AM
Points: 27, Visits: 21

How can this be done for every field in a table (at once)?
g

select max(datalength(YourColumn)) from YourTable

Post #1162511

Sean Lange

Posted Friday, August 19, 2011 9:05 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

Warren Peace (11/2/2007)

I got two different results. The first returned 75 and the other returned 150. I double checked it by writing a vbscript to get the count of each record length and 75 was correct.

Thanks everyone.

You probably have a nvarchar column? the len function returns the storage space of the data which is 2 bytes per character. The datalength function returns the number of characters in the data. Hence the reason you got 150 with len and 75 with datalength.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1162580

Sean Lange

Posted Friday, August 19, 2011 9:06 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

Gale (8/19/2011)

How can this be done for every field in a table (at once)?
g

select max(datalength(YourColumn)) from YourTable

Just add more columns

select max(datalength(YourColumn)) as YourColumnLength, max(datalength(MyColumn)) as MyColumnLength from YourTable

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1162582

Gale

Posted Friday, August 19, 2011 9:23 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 03, 2012 10:13 AM
Points: 27, Visits: 21

Sean,
Thanks.
Is there some way of just referencing the filename and having the Max(len(
done on every field within the filename.
We're forced to upload everything as varchar(255) or more and then figure out
what it should be or if the data is incorrect. Old game, same issues.
I'm just looking for a better way to do this.
Gale

Post #1162596

Sean Lange

Posted Friday, August 19, 2011 9:29 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

Gale (8/19/2011)

Wow just realized you resurrected a 4 year old thread. Smile

I am not sure what you mean about the filename. Are you importing data from a file and you need to look at datalength of the data after import before you decide what to do with it? If so, i would recommend importing to a holding table of some sort and then figure out what you need to do with it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1162602

« Prev Topic | Next Topic »

Permissions