CODEWITHSUNDEEP
sql-server-2008-r2
DonMax
DonMax

Reputation: 960

SQL Between Alphanumeric value

I have an Alphanumeric column in my db table. For my filter, I was using between to get the result filter value. Everything is okay. But, In some cases it misses some of the data's from filtering. Here are my samples,

Sample data

ACQPO14
002421
ACQPO8
ACQPO14
ACQPO19
DUMMY0001

Sql Query

SELECT po.No,
   po.PoS
FROM PoDetails pod
INNER JOIN Pors po ON po.Id=PoD.PoId
WHERE po.No BETWEEN 'ACQPO1' AND 'ACQPO20'

For the above sample. the query returns only ACQPO14 and ACQPO19 NOT ACQPO8.

Any help to this issue will be appreciated.

Thanks

Upvotes: 4

Views: 6544

Answers (5)

maldy
maldy

Reputation: 1

Just use range 10 to 20 and it works!

SELECT po.No,
   po.PoS
FROM PoDetails pod
INNER JOIN Pors po ON po.Id=PoD.PoId
WHERE po.No BETWEEN 'ACQPO10' AND 'ACQPO20'

Upvotes: 0

Amar
Amar

Reputation: 109

FOR MYSQL:-

SELECT * FROM my_table WHERE SUBSTR(seq_num, 1,3) = 'ABC' /* verifying prefix code */ AND REPLACE(seq_num, 'ABC', '') >= 61440 AND REPLACE(seq_num, 'ABC', '') <= 61807

OR

SELECT * FROM my_table WHERE SUBSTR(seq_num, 1,3) = 'ABC' /* verifying prefix code */ AND substr(seq_num, 4, length(seq_num)) >= 61440 AND SUBSTR(seq_num, 4, LENGTH(seq_num)) <= 61807

works for (like) :

ABC61447, ABC61448, ABC61545, ..., ..., ABC61807

Upvotes: 0

bgs
bgs

Reputation: 3223

If ACQPO is fixed then try below in SQL Server

SELECT po.No, po.PoS
FROM PoDetails pod
INNER JOIN Pors po ON po.Id=PoD.PoId
WHERE left(po.No,5) and 
cast(substring(po.No,6,len(po.No)) as int)
BETWEEN cast(substring('ACQPO1',6,len(po.No)) as int)  AND cast(substring('ACQPO20',6,len(po.No)) as int)


SELECT substring(data,6,len(data)),* FROM #Temp Where 
left(data,5) ='ACQPO' And 
cast(substring(data,6,len(data)) as int)
BETWEEN cast(substring('ACQPO1',6,len(data)) as int)  AND cast(substring('ACQPO20',6,len(data)) as int)

Upvotes: 0

Ravi K Thapliyal
Ravi K Thapliyal

Reputation: 51711

This is how you would do this in Oracle

SELECT po.No, po.PoS
FROM PoDetails pod
INNER JOIN Pors po ON po.Id=PoD.PoId
WHERE SUBSTR(po.No, 1, 5) = 'ACPQO'
 AND TO_NUMBER(SUBSTR(po.No, 6, 2)) >= 1
 AND TO_NUMBER(SUBSTR(po.No, 6, 2)) <= 20;

First SUBSTR() is used to match the textual part of the value. Then the numeric part of the values is parsed into a number using TO_NUMBER() and made available for numeric comparison between 1 and 20. (Other databases would also have similar functions to do the same.)

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

It makes sense as it is just text.

1 comes before 8 so, ordering in text (left to right) the db will disregard the last digit of ACQPO14 to compare it against ACQPO8. So ACQPO1 (4 removed) comes before ACQPO8 and ACQPO2 (0 removed) comes before ACQPO8 as well. So it gets filtered out by the between.

The only way for you to fix this is to parse the column by splitting it. EG: If ACQPO is a fixed-length prefix you can use some DBMS function (you haven't specified any) to trim that part and turn into a numeric format the rest. Then compare/filter by that numeric remainder.

Upvotes: 1

Related Questions