Compare last two digits with a range. Help Please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Compare last two digits with a range. Help Please

Author

Topic

unclebobness
Starting Member

8 Posts

Posted - 2011-11-03 : 03:51:12

Hi guys,

I'm trying to filter data from a table using the last two digits of the items ID, the last two digits have to be between 3-7 and I've tried using

Select * FROM table
Where ID LIKE '%[3-7]'

but this didn't work. Any tips?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-03 : 03:54:12

WHERE ID LIKE '%[3-7][3-7]'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-03 : 03:54:48

[code]LIKE '%[3-7][3-7]'[/code]

KH
[spoiler]Time is always against us[/spoiler]

unclebobness
Starting Member

8 Posts

Posted - 2011-11-03 : 04:03:53

Still not working :(, get even more random data being filtered down.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-03 : 04:12:19

please post some of the example of such data

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-03 : 04:14:27

You'll need to provide us some samples then. Maybe we are misunderstanding you.

My small test works fine:

declare @t table (c1 smallint)

insert into @t values(236)
insert into @t values(145)
insert into @t values(42)
insert into @t values(1000)

select * from @t where c1 like '%[3-7][3-7]'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-03 : 04:20:13

Hi,

Please check Columnname like '%[3-7][3-7]' works fine

Check this below sample

DROP TABLE #t
CREATE TABLE #t(dailywages INT)

insert into #t values(863)
insert into #t values(747)
insert into #t values(995)
insert into #t values(99)

select * from #t where dailywages like '%[3-7][3-7]'

If still you have problem then please post your table structure with some sample data. This will be helpful to every one.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

unclebobness
Starting Member

8 Posts

Posted - 2011-11-03 : 05:01:32

Example Data

Customer	Id

Customer A	124282
Customer B	130685
Customer C	7158
Customer D	7159
Customer E	7163
Customer F	18941
Customer G	133235
Customer H	108480
Customer I	14040
Customer J	1584

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-03 : 05:02:37

Using the sample you posted, what rows do you expect to be returned?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

unclebobness
Starting Member

8 Posts

Posted - 2011-11-03 : 05:05:17

Customer J, Customer G, Customer E and Customer B as their last digit falls between 3-7 but this isn't happening.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-03 : 05:08:09

you wanted to verify with the only the last digit or last two digits ?

use this if it is only the LAST digit
LIKE '%[3-7]'

or this if it is for the LAST TWO digits
LIKE '%[3-7][3-7]'

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-03 : 05:10:09

Do you just need one to match or both digits? The code we posted is assuming both digits must match the criteria. It seems like you are saying only one of them needs to, but I want to confirm it with you.

I was super slow on this one!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

unclebobness
Starting Member

8 Posts

Posted - 2011-11-03 : 05:11:24

Sorry for any confusion I might have caused. In the first two filters only the first digit would need to be check but in the remaining 10 the last two need to be checked and [code].. LIKE '%[82-89]' doesnt work for this aswell..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-03 : 05:14:41

quote:
Originally posted by unclebobness

Sorry for any confusion I might have caused. In the first two filters only the first digit would need to be check but in the remaining 10 the last two need to be checked and [code].. LIKE '%[82-89]' doesnt work for this aswell..

OK. now i am really confused.

KH
[spoiler]Time is always against us[/spoiler]

unclebobness
Starting Member

8 Posts

Posted - 2011-11-03 : 05:17:42

Ok, for the last filter I have to create I have to select ONLY the data wherein the last two digits of the ID fall between 82-89. Does this make more sense?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-03 : 05:18:14

quote:
Originally posted by khtan

quote:
Originally posted by unclebobness

Sorry for any confusion I might have caused. In the first two filters only the first digit would need to be check but in the remaining 10 the last two need to be checked and [code].. LIKE '%[82-89]' doesnt work for this aswell..

OK. now i am really confused.

KH
[spoiler]Time is always against us[/spoiler]

Yeah me too. It's after 2am here and can't focus enough. Working on a critical production issue, otherwise I'd be sleeping.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-03 : 05:19:49

quote:
In the first two filters only the first digit would need to be check

what first two filters ?
Check the first digit with what condition ? 3-7 also ?

quote:
in the remaining 10 the last two need to be checked

Remaining 10 of what ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-03 : 05:22:53

quote:
Originally posted by unclebobness

Ok, for the last filter I have to create I have to select ONLY the data wherein the last two digits of the ID fall between 82-89. Does this make more sense?


convert(int, column) % 100 between 82 and 89

KH
[spoiler]Time is always against us[/spoiler]

unclebobness
Starting Member

8 Posts

Posted - 2011-11-03 : 05:33:48

quote:
Originally posted by khtan

quote:
In the first two filters only the first digit would need to be check

what first two filters ?
Check the first digit with what condition ? 3-7 also ?
quote:
in the remaining 10 the last two need to be checked

Remaining 10 of what ?

Ok, the filter is a quality assurance test. There are 12 filters, the first two look for the ID to end on a specific number ie. 55 and 71. Now the other 10 look for ID's that have numbers ending with specific number ranges ie. 94-99 and when I run [code] Like '[94-99]' other ID's show up aswell.

My biggest problem is getting ID's which match the number ranges required. I hope this makes sense.

unclebobness
Starting Member

8 Posts

Posted - 2011-11-03 : 05:47:50

Okay finally got it working using the

Right(ID, 2) BETWEEN 94 AND 99

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources