1

How to get records which contain alphaNumeric characters + white-spaces. OR At-list single numeric character in name.

i.e spiderman 1, abc12 part1

What I have done.: 

 select * from table t where t.name REGEXP '^[A-Za-z0-9]+$'

but it will gives only records which dont have white space : i.e abc123

so I also tried 

  select * from table t where t.name REGEXP '^[A-Za-z0-9 ]+$'

Now, it gives me some records which does not contain any numeric characters. i.e abcdefg hij

user3379655
  • 258
  • 1
  • 5
  • 17
  • What does "OR At-list single numeric character" mean? It sounds like you actually mean "AND at least one numeric character". – Barmar Mar 20 '14 at 08:51
  • @ Mr.Barmar sir, :: OR At-list single numeric character it mean: t.name =1 is valid record which i want but t.name = aaaBBB is not valid , which i dont want. in More detail : i want records like : 1, 1SpiderMan, 1 spiderman-Part 2, , while I dont want : spider_man, spiderMAN spider M A N, this records ... in hope this is understandable , sorry for my poor English..n You are right "At-list single numeric character in name." is enough for my que.. thank you so much for your valuable time and code to solve this . – user3379655 Mar 20 '14 at 09:59

3 Answers3

2
SELECT *
FROM table
WHERE name REGEXP '^[a-zA-Z0-9 ]+$' AND name REGEXP '[0-9]'

Simpler:

SELECT *
FROM table
WHERE name REGEXP '^[a-zA-Z0-9 ]*[0-9][a-zA-Z0-9 ]*$'
Barmar
  • 741,623
  • 53
  • 500
  • 612
0

What about this: ^[\w\s]*$, which will match any numbers or word characters or whitespaces.

Elfentech
  • 747
  • 5
  • 10
  • 1
    MySQL regular expressions don't support escape sequences `\w` and `\s`. You have to use character classes `[[:alnum:][:blank:]]`. – Barmar Mar 20 '14 at 08:55
0

A digit may appear in the start, middle, or end. So we have to take care of these combinations too for which regex groups are used, like this:

select * 
from table t 
where t.name REGEXP '^[a-zA-Z ]+[0-9 ]+[a-zA-Z ]*$|^[0-9]+[a-zA-Z ]+[0-9]*$'
Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79