SQL match on letter and number arrangement

Question

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

i.e. SELECT myCol FROM myTable WHERE myCol='<letter><letter><number>'

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

I am using MySQL.

As a follow-on to this question, let's try without using REGEX: http://stackoverflow.com/questions/5064711/sql-match-on-letter-and-number-arrangement-without-using-regular-expressions — Urbycoz, Feb 21 '11 at 10:53

Martin Smith · Accepted Answer · 2011-02-18T14:47:07.730

3

I haven't got MySQL on this machine to test but I think

SELECT myCol 
FROM myTable 
WHERE myCol REGEXP '^[0-9][0-9][A-Z]$'

Should do it. ^ is an anchor for start of string, $ for end of string and the rest should be self explanatory.

Looks like you could also use REGEXP '^[[:digit:]][[:digit:]][[:alpha:]]$' but completely untested and just going off the docs.

edited Feb 18 '11 at 14:47

answered Feb 18 '11 at 14:20

Martin Smith

438,706
87
741
845

It is MySQL. How would regexp work? I've never done it before. – Urbycoz Feb 18 '11 at 14:39
you would need to use something like `WHERE myCol REGEXP '^[0-9][0-9][A-Z]$'` – Martin Smith Feb 18 '11 at 14:43
When you say "something like", I think you mean "exactly this". It's just what I needed, thanks. – Urbycoz Feb 18 '11 at 14:50
1

If it's longer, it seems you can use `WHERE myCol REGEXP '^[0-9]{x}[A-Z]{y}$'` where x is number of consecutive numbers, and y is number of consecutive letters. – Urbycoz Feb 18 '11 at 14:55

Sem Vanmeenen · Answer 2 · 2011-02-18T14:34:24.223

1

For sql server, you can use with PATINDEX. For mysql, you have REGEXP.

EDIT : Damien_The_Unbeliever pointed out I was wrong and that PATINDEX does not support regular expressions. That's correct but after some googling I found that you can use regular expressions in sql server because it hosts CLR.

edited Feb 18 '11 at 14:34

answered Feb 18 '11 at 14:20

Sem Vanmeenen

2,111
11
13

Describing the patterns you can pass to `PATINDEX` as regular expressions seems a little over-generous. Even it's own documentation only claims that you can pass wildcards. – Damien_The_Unbeliever Feb 18 '11 at 14:23
@Damien Correct. I edited the post. I didn't check on it because I was certain it supported regex. Shows again how wrong you can be. – Sem Vanmeenen Feb 18 '11 at 14:28

score 1 · Answer 3 · answered Feb 18 '11 at 14:25

Many database management systems support regular expressions. So, in PostgreSQL 9.x for example, you can do this . . .

create table mytable (
  mycol varchar(10) primary key
);

insert into mytable values 
('12X'),
('34Y'),
('123X'),
('34YY');

And then

select * 
from mytable
where mycol ~ ('^[0-9][0-9][A-Z]$');

score 1 · Answer 4 · answered Feb 18 '11 at 14:27

1

And Oracle's version.. here it is:

SELECT myCol
  FROM myTable
 WHERE REGEXP_LIKE(myCol, '^\d{2}[A-Z]$')

answered Feb 18 '11 at 14:27

Chandu

81,493
19
133
134

SQL match on letter and number arrangement

4 Answers4

Linked