Between + '%'

helloo

I have this table
Id Code
-- --
1 10101001
2 10101002
3 10102001
4 10102002
5 60101001
6 60101002
7 60102001
8 60102002
9 60201001
10 60201002
11 70101001
12 70101002

I need to query this table by the following
(select id, code from table1
where code between '1' + '%' and '7' +'%')

to get all values of codes between (code starting with 1) and (code starting with 7)

or for example:
(select id, code from table1
where code between '602' + '%' and '7' +'%')

-
Im not getting correct answers or let be specific in the second query im not getting codes starting with 7, im only getting codes >= 602 but less that 7, so 7 is not included...
Any suggestions?

hi,

SELECT
ID, Code
FROM
Table1
WHERE
Code => '1%' AND Code =< '7%'

hth

Barry Andrew|||First, you don't need to concatenate the percent sign when using it as a wildcard... With that said, you can't use a wildcard in a between operation...

Try casting your Code column to an integer and using between to compare them as numbers. That is if they are all 8 characters in length, this will work nicely.

.... between 60200000 and 79999999

Phil|||thanks for this reply

but as i told codes staring with 7 are not included?|||

BilalShouman wrote:

thanks for this reply

but as i told codes staring with 7 are not included?

Ah, have you tried to see what happens once you switch the 7 for an eight? |||the user might not know the total code so he might need
codes from 6 till 7, or from 602 till 7, or from 1 till 701
any ideas?|||

BilalShouman wrote:

the user might not know the total code so he might need
codes from 6 till 7, or from 602 till 7, or from 1 till 701
any ideas?

EDIT, due to above post, SP is wrong, sorry, im thinking of another right now = )
Barry|||

Barry Andrew wrote:

BilalShouman wrote:

thanks for this reply

but as i told codes staring with 7 are not included?

Ah, have you tried to see what happens once you switch the 7 for an eight?

Stop it!!! :)

This is incorrect advice. Using your method is treating the percent sign as an ascii character, not a wildcard. You can only use the wildcard character (%) in a LIKE statement, which is designed to do pattern matching.

The ascii value for '%' is 37, while the number zero (0) is 48. That's why your code "works" on the left side, but not the right side, and why changing that to an 8 works as well.|||ugh I see what you mean. lol my apologies sir, ill just chop that SP I wrote for him and lets see if we can nail it. |||Dave:

what does char(255) stand for, and can i use this query in
dim row() as datarow=dt.select("code >= '601' and code <= '7' + Char(255)")|||

Sorry, Bilal, I deleted my post to hastily. The char (255) is the maximum possible value of a 8-bit character. Therefore, I chose to append this to your high boundary. Is this what you are looking for:

set nocount on

declare @.mock table
( [id] integer,
code varchar (10)
)
insert into @.mock values ( 1, '10101001')
insert into @.mock values ( 2, '10101002')
insert into @.mock values ( 3, '10102001')
insert into @.mock values ( 4, '10102002')
insert into @.Mock values ( 5, '60101001')
insert into @.Mock values ( 6, '60101002')
insert into @.mock values ( 7, '60102001')
insert into @.mock values ( 8, '60102002')
insert into @.mock values ( 9, '60201001')
insert into @.Mock values (10, '60201002')
insert into @.Mock values (11, '70101001')
insert into @.Mock values (12, '70101002')
insert into @.mock values (13, '70201001')
--select * from @.mock

select * from @.mock where code between '1' and '7' + char(255)

-- Output:

-- id code
-- -- -
-- 1 10101001
-- 2 10101002
-- ...
-- 11 70101001
-- 12 70101002
-- 13 70201001

select * from @.mock where code between '6' and '7' + char(255)

-- id code
-- -- -
-- 5 60101001
-- 6 60101002
-- 7 60102001
-- 8 60102002
-- 9 60201001
-- 10 60201002
-- 11 70101001
-- 12 70101002
-- 13 70201001

select * from @.mock where code between '6' and '701' + char(255)

-- id code
-- -- -
-- 5 60101001
-- 6 60101002
-- 7 60102001
-- 8 60102002
-- 9 60201001
-- 10 60201002
-- 11 70101001
-- 12 70101002

|||

Where are u guys?

any suggestions?

|||Ah you beat me to it!

Oh well, using Mugambo's spliffy code here is an SP Where you could enter changing values.

CREATE PROCEDURE stp_GetBetweens
@.numLow int,
@.numHi int
AS
SELECT
Id, Code
FROM
MyTable
WHERE
Code Between @.numLow AND @.numHi + char(255)
GO

So in your app, pass it the two values you want it to use and it does the rest.

hth

Barry Andrew|||Read up dude, I think we have this one. Teamwork |||

Thank you, Barry. I choked on my original response!

Dave