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
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 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
Dave I
Dave I
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 478
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1688 Visits: 2804
select max(datalength(YourColumn)) from YourTable
Dave I
Dave I
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 478
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
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 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 Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 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-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33168 Visits: 17669
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-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33168 Visits: 17669
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 Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 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-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33168 Visits: 17669
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