Within the confines of a SELECT statement, how can I determine if a character string is a "numeric literal"?
One way would be to CAST the value as integer:
select charcolumn , cast(charcolumn as integer) as numericcolumn from yourtable
Just kidding! The problem with this approach, of course, is that the query will fail as soon as it finds a row with a charcolumn value that isn't numeric.
Here's a method which you can use in Microsoft SQL Server or Sybase:
select charcolumn from yourtable where patindex('%[^0-9]%',charcolumn) = 0
PATINDEX is a special string function that uses a pattern to search a string. The result of the function is the character position of the first occurrence of the pattern within the string. So, for example, PATINDEX('%r%','Curly') is 3, while PATINDEX('%r%','Moe') is 0 because the pattern is not found.
In the query shown above, a special pattern is used. The square brackets indicate a range of characters, while the caret (^) means NOT. Thus the pattern '%[^0-9]%' searches for the first character that isn't a 0 through 9. If none is found, the result of the function is 0. This is what we want -- the values which do not contain a character that isn't a numeric.
In MySQL, you can use REGEXP, the regular expression operator. The equivalent example would be:
select charcolumn from yourtable where charcolumn not regexp "[0-9]"
Other databases may have similar capabilities, or allow you to create a user defined function. See the Search390 article Regular Expressions for the SQL-challenged.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.