SQL Between Range

[johnisotank]

Hi All,

could someone help me with this please.. We have an invoices table where the invNos are like 'A10710', 'A10711' ETC...

I am running a select statement saying give me all the jobs where InvNo between 'A10710' AND 'A10730'. This does give me those records but it also pulls in the following invoice numbers...

A1072
A1073

Any idea what is causing this and a possible fix?

Thanks
John

 

[Jamfool]

it depends on the data you are storing. below is one answer, but you could strip out all chars, or look for the first char moving from right to left etc.

DECLARE @tbl TABLE (items VARCHAR(50))

INSERT INTO @tbl VALUES ('A10710')
INSERT INTO @tbl VALUES ('A10711')
INSERT INTO @tbl VALUES ('A10730')

INSERT INTO @tbl VALUES ('A1072')
INSERT INTO @tbl VALUES ('A1073')

SELECT * FROM @tbl
WHERE REPLACE(items,'A','') BETWEEN 10710 AND 10730

 

[johnisotank]

Hi thanks for that, just tried it but unfortunately it still brings in those 2 invoices?

 

[NiceArms]

I tried using Jamfools code and it worked fine.

Can you post your code?

/Nice

 

[johnisotank]

Hi sorry for late reply,

code is

SELECT JobNo, InvNo
FROM tblJobs
WHERE REPLACE(InvNo,'A','') Between 'A10710' And 'A10730'

Perhaps I've done something wrong?

thanks
John

 

[gmmastros]

Just curious.... Do ALL of your invoice numbers start with the letter A?

-George

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

 

[tcsbiz]

What's causing this is the way character values are sorted. Google the net for a collating sequence table. To get the records you want requires true numeric values.

 

[Crowley16]

just remove the single quotes from your between numbers.

the single quotes are text delimiters, and is telling the system to filter the values as a text field, rather than number field.

do be careful since if a invoice starts with a 'B' or something, then there'll be problems...

--------------------
Procrastinate Now!

 

[NiceArms]

I agree with Crowley16. Remove the commas from you where clause: Between 'A10710' And 'A10730' BECOMES Between A10710 And A10730

 

[Jamfool]

if you take off the quotes,sql will ask you what it is, and most likely tell you that the column isnt there:

SELECT 'A10730'
SELECT A10730

...

In my initial post I have stripped off the 'A' (although this will all be dependant on your data, as to what you need to do)

I am then comparing the stripped numeric value to a start number and an end number.
e.g is 10710 between 10710 and 10730

The query you have written will compare the stripped numeric value (as a string) to a string range.

e.g is '10710' between 'A10710' and 'A10730'

what you do by looking at the above is say to yourself I will ignore 'A' and yes it fits nicely in.


...But it all dependants on what you want. take a simple order e.g

A1
A2
A3
B1
B2

if you just strip off the letters and order you will get 1,1,2,2,3

however...and you may wish to incorporate more complexity into your order.

 

[gmmastros]

I asked earlier if there would ever be any invoice number that does not start with A. There were a couple reasons for this question.

If ALL invoices start with A, then remove it from the data and change the invoice number column to an integer.

If you have invoice numbers other than A, I would encourage a long term fix. Obviously, the letter prefix has a meaning, and so do the numbers. So, you are storing 2 different pieces of data in the same column. The violates database normalization rules. If you separate the data in to two columns, it is EXTREMELY EASY to concatenate them together for display purposes, but you will also be able to work with the data separate, if need be.

Now, let's look at the suggestion made earlier...

DECLARE @tbl TABLE (items VARCHAR(50))

INSERT INTO @tbl VALUES ('A10710')
INSERT INTO @tbl VALUES ('A10711')
INSERT INTO @tbl VALUES ('A10730')

INSERT INTO @tbl VALUES ('A1072')
INSERT INTO @tbl VALUES ('A1073')

SELECT * FROM @tbl
WHERE REPLACE(items,'A','') BETWEEN 10710 AND 10730

Specifically, let's look at the where clause. The operator is BETWEEN. On the left side, we have a varchar column (items). We replace the letter A with nothing, but it's still a string. On the right, we have a couple of integers. When SQL Server compares a string to an integer, it first converts the string to an integer and then performs the comparison as though it were an integer.

In this case, it can cause a problem if you have invoice numbers that have characters other than A (for example, B). There is an interesting 'trick' that you can use to prevent this potential problem, AND also make the query faster.

For example, take a look at this code....

DECLARE @tbl TABLE (items VARCHAR(50))

INSERT INTO @tbl VALUES ('A10710')
INSERT INTO @tbl VALUES ('A10711')
INSERT INTO @tbl VALUES ('A10730')

INSERT INTO @tbl VALUES ('A1072')
INSERT INTO @tbl VALUES ('A1073')

INSERT INTO @tbl VALUES ('[!]B1072[/!]')
INSERT INTO @tbl VALUES ('[!]B1073[/!]')

SELECT * FROM @tbl
WHERE REPLACE(items,'A','') BETWEEN 10710 AND 10730

If you run the code above, you will get an error.
[tt][red]Conversion failed when converting the varchar value 'B1072' to data type int.[/red][/tt]

But... with this trick...

SELECT * FROM @tbl
WHERE [!]items like 'A%'[/!]
      And REPLACE(items,'A','') BETWEEN 10710 AND 10730

This will prevent the error message with the given data, but it still isn't completely safe because a value of 'AX123' will satisfy the like comparison but still fail the integer conversion. If all of your invoice numbers start with a single character followed by all numbers, then the like comparison should work ok for you.

There is another (hidden) benefit of the like comparison. Since the comparison is based on the beginning of the string, SQL Server would be able to use an index on the items column to quickly locate the data. Specifically... an index seek would be performed instead of an index scan. With a large table, the difference in time could be huge.

-George

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