Question about "between" with wildcard 3

[jenlion]

Trying to understand the behavior of between here.

I thought that the wildcard % meant 0 or more characters, but I'm running into something I don't quite understand.

create table #table1 (nothing int)
insert into #table1 values (1)
select * from #table1 where 'a' between 'a%' and 'b%'

This returns no rows.

Change it to

 select * from #table1 where 'a%' between 'a%' and 'b%'

and you get a result.

But I want 'a' to get a result.

Somebody explain the technical reason this doesn't work?

 

[yelworcm]

Wild cards are not considered with BETWEEN. Wild cards only apply to LIKE statements.

Try

select * from #table1 where 'a%' between 'a' and 'b'

 

[gmmastros]

yelworcm,

I was scratching my head for a couple minutes there until you pointed out the obvious.

Jenlion,

I suspect you will want to use something like this:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp(Data) Values('Apple')
Insert Into @Temp(Data) Values('Banana')
Insert Into @Temp(Data) Values('Grape')

Select * From @Temp Where Data like '[ab]%'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jenlion]

Duh. OK.

Here's my actual problem. They want to give the starting characters of a GL account number, and the starting characters they use could vary -- they might give 1000 or 1000-10 as the starting. So, if they want all transactions where the gl account numbers are those starting between 1000 and 2230, results would include 1000-101-010, 1500-233-220, and 2230-000-234.

On top of that the transaction table has several million rows, so I have to do it efficiently. Tring to make this query as flexible as possible for them -- they want to give or not give several other variables, and that's all working -- it's just this "starting with" between giving me a headache!

 

[markros]

Try

@EndNumber = @EndNumber + replicate('z',length of the field - len(@EndNumber))

myNumber between @BegNumber and @EndNumber

Now, how can we get the length of the field in run-time without hardcoding?

 

[markros]

Though you can just add any big number of z at the end - it would work.

 

[gmmastros]

Now, how can we get the length of the field in run-time without hardcoding?

Declare @ColumnLength Int

Select @ColumnLength = Character_Maximum_Length 
From   Information_Schema.Columns 
Where  Table_Name = 'YourTableName' 
       And Column_Name = 'YourColumnName'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Tx

 

[jenlion]

I think you guys have the idea. I'll run with that and see if the time it takes to run is acceptable. Thanks!!