SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get longest length of data in a column


Get longest length of data in a column

Author
Message
Warren Peace
Warren Peace
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2245 Visits: 476
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
DaveItz
DaveItz
SSChasing Mays
SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)

Group: General Forum Members
Points: 658 Visits: 483
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


Pam Brisjar
Pam Brisjar
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4848 Visits: 2804
select max(datalength(YourColumn)) from YourTable
DaveItz
DaveItz
SSChasing Mays
SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)

Group: General Forum Members
Points: 658 Visits: 483
I wanted to give him a little more information than that.
I figured he would be able to pull that one off by himself.
Warren Peace
Warren Peace
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2245 Visits: 476
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.
Gale
Gale
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 21
How can this be done for every field in a table (at once)?
g

select max(datalength(YourColumn)) from YourTable
Sean Lange
Sean Lange
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101428 Visits: 18184
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 Modens 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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101428 Visits: 18184
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 Modens 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)
Gale
Gale
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101428 Visits: 18184
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search