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

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.