SQL match on letter and number arrangement without using regular expressions

Question

Is it possible to write an SQL statement for MySQL which finds all rows which match a specific arrangement of letters/numbers without using REGEXP?

i.e. SELECT myCol FROM myTable WHERE myCol='{number}{number}{letter}'

Should return 12X and 34Y but not 123X or 34YY

[I have asked a similar question before- ( SQL match on letter and number arrangement). The difference is that I have discovered that I cannot use regular expressions with the ADO.Net driver I am using. Whatsmore, I cannot update it since I am using Visual Studio 2003 which is not compatible with later versions.]

Answer 1

Try this:

SELECT myCol 
FROM myTable 
WHERE SUBSTRING(myCol, 1 , 1) >= 'A' 
AND SUBSTRING(myCol, 1 , 1) <= 'Z' 
AND SUBSTRING(myCol, 2 , 1) >= 'A' 
AND SUBSTRING(myCol, 2 , 1) <= 'Z' 
AND SUBSTRING(myCol, 3 , 1) >= '0' 
AND SUBSTRING(myCol, 3 , 1) <= '9'

Answer 2

You can test the first two digits using LIKE, and for the third use an ascii range. The first two digits can use an ascii range too, but LIKE should be better.

select *
from tbl
where length(n) = 3
  and '0123456789' like concat('%',mid(numcol,1,1),'%')
  and '0123456789' like concat('%',mid(numcol,2,1),'%')
  and ASCII(upper(MID(numcol,3,1))) between 65 and 90

Answer 3

SELECT myCol 
FROM   myTable 
WHERE  ( myCol LIKE '12X%' OR myCol LIKE '34Y%' ) 
AND    myCol NOT LIKE '34YY%'

Rough example, but should help you figure the problem out. Can't help more since your example isn't that precise.

Answer 4

If your query is as simple as your example (ie. no parameters) then can you use a Stored Procedure to return the results using Reg Exp? In that case the driver shouldn't know or care that you're using Reg Exp.

Answer 5

Well, yes, but it would be very slow and resource-intensive ...

SELECT 
    myCol 
FROM 
    myTable 
WHERE 
    CHAR_LENGTH(myCol) = 3 
    AND SUBSTRING(myCol, 1, 1) IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') 
    AND SUBSTRING(myCol, 2, 1) IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') 
    AND SUBSTRING(myCol, 3, 1) IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z')

That'll match "12X" and "34Y" but not "123X" or "34YY".

Answer 6

Try this -

SELECT myCol FROM myTable WHERE myCol REGEXP '[[:alpha:]]{2}[[:digit:]]{1}'

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Answer 7

If you are using EMS SQL Manager for MySQL, you can use RegEx to filter data.

SELECT col 
FROM tbl
WHERE col REGEXP '^[[:alpha:]]{2}[[:digit:]]{1}$'

RegEx:

^[[:alpha:]]{2}[[:digit:]]{1}$

^ = starts with

[[:alpha:]] = alphabet

[[:digit:]] = numbers

{n} = exactly n instances of

Hope this helps.

Answer 8

Just to state the accepted answer explicitly:

SELECT myCol 
FROM myTable 
WHERE SUBSTRING(myCol, 1 , 1) BETWEEN 'A' AND 'Z' AND
SUBSTRING(myCol, 2 , 1) BETWEEN 'A' AND 'Z' AND
SUBSTRING(myCol, 3 , 1) BETWEEN '0' AND '9'

(mixture of Ken's answer and Andriy's suggestion)