Wildcards in a query

Question

Hi all,

I need to extract a range of ICD9 codes (varchar 6) from a table. The codes are 5 characters in length (even though the attribute has 6 characters).

What I don't know how to do write a query to extract a range of values based on the first three characters. I was thinking I use a range of values, but I thought you can't use a LIKE.

I need to extract codes from 480 through 488 (including 488). For example, the actual values in the table would be 480.1, 480.22, 481, 482.22, etc. I would need all rows with staring with 480 through value starting with 488.

How would my query statement look.

Thank you.
Dave

Answer 1

   Try this..

      

declare

 

@sample table (id float)

insert

 

into @sample

select

 

480.1 union all

 

select 481 union all

select

 

480.22 union all

 

select 480.22

select

 

left(id,3) from @sample where left(id,3) between 480 and 488

---

Lakshman

Answer 2

Try if this logic works out for your requirement.

declare @tab table(ICDCode varchar(6))
Insert into @tab
Select '479wew' union
Select '480wef' union
Select '481wef' union
Select '482wef' union
Select '483wef' union
Select '484wef' union
Select '485wef' union
Select '486wef' union
Select '487wef' union
Select '488wef' union
Select '489wef' union
Select '490wef'

select * from @tab where convert(int,substring(ICDCode,1,3)) between 480 and 488

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Answer 3

Try this:

declare @t table (a varchar(6)) 

insert into @t 

select 480.1 union all
select 481 union all 
select 480.22 union all
select 480.22 union all
select 488.1 union all
select 489.5

select a from @t where a like '48[0-8]%'

Answer 4

Thanks all for you help and solutions. I am going to use Ana's solution, but all worked.

Thanks again!
Dave