1

In the table EMPLOYEE I am having a column EMP_NAME with varchar(100) type. I want to find all such EMP_NAME which are numeric and does not contain alphabet. For that I use REGEXP, it is working fine. I tried for another logic, the query is given below:

select emp_name from test.employee where lower(emp_name)=upper(emp_name);

but it is returning all the names(both numeric and alphabetical). Please tell me what is wrong with this query?

Prashant
  • 692
  • 2
  • 11
  • 26
  • mysql has a case sensitive mode that is optionally on. Yours is off. http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html – dcaswell Sep 06 '13 at 05:10
  • but this wont eliminate special characters, only alphabets will get filtered – Deepanshu Goyal Sep 06 '13 at 05:12
  • @user814064: How to make it on? – Prashant Sep 06 '13 at 05:14
  • 1
    You can read this: http://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql though testing lower case equal to upper case to find numbers is a VERY BAD IDEA. – dcaswell Sep 06 '13 at 05:17

2 Answers2

1

try this

SELECT emp_name FROM test.employee WHERE concat('',emp_name * 1) = emp_name
Deepanshu Goyal
  • 2,738
  • 3
  • 34
  • 61
0

If you're checking for numeric values, then check for numeric values, rather than checking to see if the lowercase name is the same as the upper case name.

Besides, by your method, an emp_name of "^^^" is numeric because lower("^^^") is "^^^" which is the same as upper("^^^").

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
  • you are right I tried it with REGEXP for checking numeric names explicitly but same names should be identifiable with this logic also. Why that is not working? – Prashant Sep 06 '13 at 05:12
  • 1
    Why do you not want to use a regex? A regex is undoubtedly the better solution than going through some logic that doesn't actually answer the question you're trying to answer. If you want to know "Is this string all digits", the way to answer that is not "is the lowercase representation of the string the same as the uppercase representation of the string." Use the regex. If you have concerns about the regex, explain and we can address those concerns. – Andy Lester Sep 06 '13 at 05:15
  • If this logic works I think it will give better performance. Will REGEX be a good option in term of performance? Will you please provide a detail on the same? – Prashant Sep 06 '13 at 05:21
  • Your concern about performance is what is called "premature optimization". Unless you have actually measured a speed problem with your query, then don't worry about speed. If you do have a speed problem, then you need to measure your code with a code profiler instead of guessing at where the speed problem might be. I can guarantee you that a regex match is not going to be the cause of any slowdowns on the type of query that you're doing. Final thought: It doesn't matter how fast the code is if it doesn't work. The regex works, and your solution that you're guessing is faster does not. – Andy Lester Sep 06 '13 at 05:24
  • I'm not actually concerned about any performance comparision right now, I'm just wondering what is the issue with my logic which technically seems correct. May be knowing this problem will aware me the correct usage of such functions in future that's why I am curious to know. – Prashant Sep 06 '13 at 05:29