HI, I have a table where the varchar field is used to store a alphanumeric values. I want to get the MAX value from the field without considering the alpha numeric values.
E.g
create table #Temp
(
id int,
number Varchar(50),
)
truncate table #Temp
insert into #Temp values(1,'100001')
insert into #Temp values(1,'100000')
insert into #Temp values(1,'12')
insert into #Temp values(1,'8')
insert into #Temp values(1,'5')
insert into #Temp values(1,'1d8')
insert into #Temp values(1,'20')
insert into #Temp values(1,'100000-01425')
select * from #Temp
When I select the row i will get all row, now I want MAX value of the records having only numeric values which should be in selected range.
I have tried following query but didn't worked as expected.
It returns 100001 as output.
SELECT MAX(CAST(c.number as int)) [CurrentSourceKey]
FROM #Temp c
WHERE c.number not like '%[a-z]%' AND
ISNUMERIC(c.number) = 1 AND
c.number BETWEEN '1' AND '20'
Output should be
21
please help.
Thanks
Girish SapariyaPosted Jun 7, 2016, 2:13 AM
Thanks Sonu Chaudhary
Sonu ChaudharyPosted Jun 6, 2016, 1:34 PM
SELECT MAX(CAST(c.number as int)) aa
FROM #Temp c
WHERE c.number not like '%[a-z]%' AND
ISNUMERIC(c.number) = 1 AND c.number like '__' and
c.number BETWEEN '1' AND '20'