Regular expression for finding tag numbers in a list of cats

Hong Xia Published at Dev

Hong Xia

I am trying to match the tag numbers in a list of cats:

Abyssinian 987
Burmese a1a
Dragon Li 2B
987 Cat
cat 987 Toyger
cat A1A Siamese
1

The tag numbers for the list of cats would be:

987
a1a
2B
987
987
A1A
1

I've tried using the regular expression:

\b[0-9a-zA-Z]{1,3}\b

The problem is that it will match "cat" and "Li" (in Dragon Li). It should only match the tag number.

The requirements for a tag number are:

1-3 characters, it must contain at least one integer (0-9)
It can appear at any place in the string

As a side note, I am using Postgres regular expressions, which I think use POSIX regular expressions. (http://www.postgresql.org/docs/9.3/static/functions-string.html)

Erwin Brandstetter

This works in PostgreSQL:

SELECT substring(cat FROM '\m(?=\w{0,2}\d)\w{1,3}\M') AS tag
FROM   cat;

\m and \M .. beginning and end of a word.
(?=\w{0,2}\d).. positive lookahead
\w{1,3} .. 1-3 word characters

Assuming there is a single match in every string, substring() (without the "global" switch 'g') is better for the job than regexp_matches(), which would return an array (even for a single match).
substring() is also a bit faster.

SQL Fiddle.

Collected from the Internet

Please contact [email protected] to delete if infringement.