CODEWITHSUNDEEP
mysqlregex
Jackson
Jackson

Reputation: 1526

How to search by specific pattern in MySQL?

I have below pattern of data in field

XXX-XX-XXX

Some of the data don't have that patterns.

So need to search those records.

SELECT *  FROM table WHERE `name` NOT REGEXP '^.{10}$'
SELECT *  FROM table WHERE `name` NOT REGEXP '^..........$'

Above 2 queries works fine. But not 100%.

Can I filter by {3}-{2}-{3} ?

Upvotes: 2

Views: 178

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626747

You want to match a string with 3 alphanumeric chars followed with -, followed with 2 alphanumeric and then again a hyphen and 3 last alphanumeric in the string.

Use

'^[[:alnum:]]{3}-[[:alnum:]]{2}-[[:alnum:]]{3}$'

Details

  • ^ - start of the string
  • [[:alnum:]]{3} - 3 alphanumeric chars
  • - - a hyphen
  • [[:alnum:]]{2}- - 2 alphanumeric chars and a -
  • [[:alnum:]]{3} - 3 alphanumeric chars
  • $ - end of string.

See MySQL "Regular Expression Syntax":

Character Class Name   Meaning
alnum                               Alphanumeric characters

Upvotes: 2

Related Questions