CODEWITHSUNDEEP
sqlsql-server
nobody
nobody

Reputation: 11080

Filter records based on numeric value when column type is varchar

I have a column in a table with datatype varchar that stores version numbers.I need to filter and select only version numbers that are less than equal to 5

Input

5.0.0.330
Eclair
5.0.0
5.0.0.591
5.0.0.405
6.0.0.522
4.0.2
7.1.0.205
5.0.0.592
2.3.4-ez
4.2.2-2013-12-11-V1.0
4.6.0.304
nubernel-2.6.35_v0.0.1
2.1-update1
2.3

Output

5.0.0
4.0.2
2.3.4-ez
4.2.2-2013-12-11-V1.0
4.6.0.304
2.1-update1
2.3

I can get all the versions less than 5 by converting the first character of the varchar column.However I can't figure out how to include the 5.0.0 version in the result set.

select distinct os_ver,substring(os_ver,1,1)  
from 
    dbo.mytable 
where 
    os_ver like '[0-9]%' and cast (substring(os_ver,1,1) as int) < 5

This gives me all version less than 5 except the version 5.0.0

4.0.2
2.3.4-ez
4.2.2-2013-12-11-V1.0
4.6.0.304
2.1-update1
2.3

Upvotes: 1

Views: 577

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Select * 
 From  dbo.mytable 
 Where os_ver<='5.0.0'

Returns

os_ver
5.0.0
4.0.2
2.3.4-ez
4.2.2-2013-12-11-V1.0
4.6.0.304
2.1-update1
2.3

Upvotes: 1

Aram
Aram

Reputation: 5705

Try this condition.

select * from (
select '5.0.0.330' as a  union
select 'Eclair' union
select '5.0.0' union
select '5.0.0.591' union
select '5.0.0.405' union
select '6.0.0.522' union 
select '4.0.2' union
select '7.1.0.205' union
select '5.0.0.592' union
select '2.3.4-ez' union
select '4.2.2-2013-12-11-V1.0' union
select '4.6.0.304' union
select 'nubernel-2.6.35_v0.0.1' union
select '2.1-update1' union
select '2.3') b
where a <= '5.0.0' and ISNUMERIC(SUBSTRING(a, 1, 1)) = 1

Upvotes: 0

Related Questions