select max(string)

[Zeyad Jalil]

Hi All,,, I want To Get the max value of a string from a database,,, such as I store a value A1-50,A2-50,B1-50,B2-50,,, I want To Get the max value from these string, but i cann't do that. Please Help Me. Thanks All,,,,

[Xandip]

i think this is what you meant.

select top 1 len{your string column} from order by len{your string column} desc
Or
select max{len{your string column}} from

hope it helps.

The name is Sandeep

[Syed Mehroz Alam]

If you need to get the maximum length string, you could simply use a Select Max(Len(StringColumn)) expression. If you have any custom criteria for getting the maximum sub-string from that comma separated list, I suggest you create a scalar valued function, e.g. GetMax(varchar (nn)). Inside that function you could parse that comma separated list into table rows (you can easily find such functions on the internet) and then apply your maximum criteria. Hope that helps. Regards, Syed Mehroz Alam.

My Blog My Articles Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

[Zeyad Jalil]

Hi,, I don't want the Len Of string,, but i want the max value , like A9-50,A10-50, when get max, will return A10-50 thanks all.

[Zeyad Jalil]

Hi,, I don't want the Len Of string,, but i want the max value , like A9-50,A10-50, when get max, will return A10-50 thanks all.

[Ashfield]

Actually the max value of A10-50 and A9-50 is A9-50 as you are doing string comparisons and A9 is larger than A1. What you are trying to do is rather complex unless you can guarantee the format of the data as you need to reformat it for comparison. What you are trying to get is A10-50 and A09-50, then the comparison will work, so you have to split out the alpha at the start, reformat the first number and then reformat the last number (again, A1-9 is greater than A1-10). It is possible, but not easy - then who said coding should be easy, thats what we get paid for :)

Bob Ashfield Consultants Ltd

[Paddy Boyd]

There was a very similar question a couple of days ago, with a good suggestion to include another field in your table that just holds the 'numeric' part of your field, to be used in comparisons such as this.

[RGTuffin]

If you stored your values in the same format eg. A01-50, A02-50, A09-10, A10-50 Then a max on the column would bring back the A10-50 you wanted.

[Wendelius]

The answer Ashfield gave you is correct. To give you some starting points for reformatting the values (Note, this is not the solution, just ideas you could use):

CREATE TABLE Test12 (
Column1 varchar(50)
)

insert into Test12 (Column1) values ('A1-50')
insert into Test12 (Column1) values ('A2-50')
insert into Test12 (Column1) values ('A10-50')
insert into Test12 (Column1) values ('A9-50')

SELECT Column1,
CHARINDEX('-', Column1),
SUBSTRING(Column1, 0, CHARINDEX('-', Column1)),
SUBSTRING(SUBSTRING(Column1, 0, CHARINDEX('-', Column1)), 2, 999),
REPLICATE('0', 2 - LEN(SUBSTRING(SUBSTRING(Column1, 0, CHARINDEX('-', Column1)), 2, 999)))
+ SUBSTRING(SUBSTRING(Column1, 0, CHARINDEX('-', Column1)), 2, 999)
FROM Test12

The need to optimize rises from a bad design. My articles[^]