Problem solve Get help with specific problems with your technologies, process and projects.

Detecting a numeric value in a character column

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

This was last published in March 2003

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.