0

I want to remove the names which may be registered with fake names.

As the developer forgot to put validation on form registration. Now i want to remove the fake names.

And for checking if that name is fake or not, I am checking if the name content any numbers or not ?

This is my query which i have written but its not working...

SELECT registration.regi_id, student.first_name, 
student.cont_no, student.email_id, 
registration.college, 
registration.event_name, 
registration.accomodation
FROM student, registration
WHERE student.stud_id = registration.stud_id
    AND student.first_name NOT RLIKE '%[0-9]%'

How to fix this problem ?

Sorry for my language issues,

P.S. There are many names in "first_name" field like "asdfasdf12323", i don't want that kind of names to be shown on list.

Nitz
  • 1,690
  • 11
  • 36
  • 56
  • What is the actual problem you're having? – Infiltrator Sep 04 '12 at 06:19
  • You use a regex like and using normal like wildcards. Those do not like eachother as far as I know. Replace % with .* . And should the NOT be there? Not sure which way you want to match – René Sep 04 '12 at 06:20
  • 1
    Are you aware that you've got `NOT` in there? Or are you only supposed to lists students whos name _isn't_ fake? – Jørgen R Sep 04 '12 at 06:21
  • I want to remove the name which is having numbers also. – Nitz Sep 04 '12 at 06:28
  • as much as I can see the point of "asdfasdf12323" being a bad name, trying to validate names is always going to be problematic, because real people do have odd names. See also my answer to a similar question here: http://stackoverflow.com/questions/3853346/how-to-validate-human-names-in-cakephp/3853820#3853820 – Spudley Sep 04 '12 at 06:45

2 Answers2

0

Your column may contain Alphanumeric characters also.YOu need to filter Numbers and Alphanumeric characters both

For Alphanumeric characters Try REGEXP '^[A-Za-z0-9]+$'

For numbers Try REGEXP '[0-9]'

Shail
  • 1,565
  • 11
  • 24
0

Well as far as the regex is involved, your expression is only looking for a single number. Also, your 'NOT RLIKE' isn't using regex but is doing a basic string search for the literal '[0-9]' I believe. MySql has support for regex, and your last clause would look like so: AND student.first_name NOT REGEXP '[0-9]*'

Brandon Miller
  • 2,247
  • 8
  • 36
  • 54
  • Or, instead of testing for whats NOT in the first_name column, you could check it IS a compatible string format. `AND student.first_name REGEXP '[A-Za-z]*'` Checks if the string ONLY contains letters in the alphabet – Brandon Miller Sep 04 '12 at 06:28
  • 1
    @BrandonMiller That implied that names only contain the characters a-z, which they do not (at least mine doesn't). `\w+` would be better – Jørgen R Sep 04 '12 at 06:43
  • @jurgemaister OP specifically states he does not want numbers in his names, only letters EDIT: Ah, I think im having a problem with negation, i removed the NOT, I thought you were checking to see what names were valid – Brandon Miller Sep 04 '12 at 06:51
  • @BrandonMiller Well, a-z still doesn't cover it. What about é, ã, ö, æ and other international glyphs? – Jørgen R Sep 04 '12 at 06:52
  • @jurgemaister Not only did I not factor in my removal of NOT, which is incorrect for he is checking what names SHOULDNT contain, but no I did not factor in glyphs, thanks for that. Well, I'm not sure if `NOT REGEXP '[0-9]*'` would work although I think that would, your suggestion seems much better. – Brandon Miller Sep 04 '12 at 06:55