I have a varchar column in SQL and would like to select a max number from it.
So this is how it looks.
ID col1
A 0111211
B 23212321
C 5112012
I want to be able to select the MAX digit from the string. For example; for ID A it will be 2.
I have tried
select ID, max(col1) from TableA
group by ID
And it selected the entire number.
Does anyone know how to do this?
SQL does not have very good string manipulation functions. That is one reason why you do not want to do complex encoding of values into strings. Your structure suggests that you should have a separate row for each value.
But, this might be the data model you are stuck with.
In most dialects of SQL, a brute force approach is perhaps the most feasible method. The following assumes that the individual values are only digits:
select id,
(case when col1 like '%9%' then 9
when col1 like '%8%' then 8
when col1 like '%7%' then 7
when col1 like '%6%' then 6
when col1 like '%5%' then 5
when col1 like '%4%' then 4
when col1 like '%3%' then 3
when col1 like '%2%' then 2
when col1 like '%1%' then 1
when col1 like '%0%' then 0
end) as col1_digit_max
from tablea;
Note that no aggregation is needed.
(sort_desc(string_to_array(col1, null)::int[]))[1]