Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get longest length of data in a column Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2007 5:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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
Posted Tuesday, October 30, 2007 9:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, October 30, 2007 9:44 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 1, 2014 7:26 AM
Points: 908, Visits: 2,804
select max(datalength(YourColumn)) from YourTable
Post #416865
Posted Tuesday, October 30, 2007 10:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Friday, November 2, 2007 10:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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
Posted Friday, August 19, 2011 8:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 3, 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
Posted Friday, August 19, 2011 9:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1162580
Posted Friday, August 19, 2011 9:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1162582
Posted Friday, August 19, 2011 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 3, 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
Posted Friday, August 19, 2011 9:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
Gale (8/19/2011)
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


Wow just realized you resurrected a 4 year old thread.

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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1162602
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse