September 9, 2008 at 11:20 pm
Hi
I have a table which has column that contains data in range and csv format.
like
table1
col1 col2 col3..........
a 6-10,4,5 hjkj..............
b 0 81 uiu....
c 5-16,3,6,50 jhlkjlk........
I want to get the greatest of the number from this string.
say in col1 greatest no is 10 , in col2 its 81, in col3 it is 50
how to get this greatest no
September 12, 2008 at 1:46 pm
Hi Khushbu,
Below is my answer to your problem, you should create a function, then you can make a max.
alter function dbo.token_char
(
@col1char(1),
@col2varchar(200)
)
RETURNS @tokens table (
col1 char(1),
col2 varchar(200)
)
as
begin
declare @var varchar(200)
set @var=replace(replace(replace(@col2,' ','.'),'-','.'),',','.')
while len(@var)>0
begin
if CharIndex('.',@var)<>0
begin
insert into @tokens (col1,col2)
values(@col1, substring(@var,1,CharIndex('.',@var)-1))
set @var=substring(@var,CharIndex('.',@var)+1,len(@var))
end
else
begin
insert into @tokens (col1,col2)
values(@col1, @var)
set @var=''
end
end
return
end
select b.col1,max(cast(b.col2 as int))
from (
select 'a' as col1, '6-10,4,5' as col2, 'hjkj....' as col3 union all
select 'b' as col1, '0 81' as col2, 'uiu....' as col3 union all
select 'c' as col1, '5-16,3,6,50' as col2, 'jhlkjlk....' as col3
) a
cross apply dbo.token_char(a.col1,a.col2) b
group by b.col1
Thanks,
Jorge.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply