Within the confines of a SELECT statement, how can I determine if a character string is a "numeric literal"?
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.