October 30, 2007 at 5:51 pm
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
October 30, 2007 at 9:07 pm
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
October 30, 2007 at 9:45 pm
select max(datalength(YourColumn)) from YourTable
October 30, 2007 at 10:48 pm
I wanted to give him a little more information than that.
I figured he would be able to pull that one off by himself.
November 2, 2007 at 10:18 am
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.
August 19, 2011 at 8:09 am
How can this be done for every field in a table (at once)?
g
select max(datalength(YourColumn)) from YourTable
August 19, 2011 at 9:05 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2011 at 9:06 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2011 at 9:23 am
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
August 19, 2011 at 9:29 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply