How to Check a String to See if the Value Is Numeric in SQL
Structured query language (SQL) is a standardized language used for querying and managing data in databases. An SQL query is the name given to an SQL statement or series of statements executed on a database. Any database can implement SQL. When performing a SQL query, you may want to find out whether a string --- any collection of textual data --- contains only a numeric value. Finding out whether a string is numeric can prove useful --- for instance, you can perform mathematical functions on numerical data that you cannot on strings. You may also not want a string to contain numeric data, which you can test for and act on accordingly.
Other People Are Reading
Instructions
-
-
1
Run your database, and open your usual textual SQL query generator.
-
2
Write a query that includes the value you want to check. Using SQL, you can check a field's value by using a regular expression. For example,
SELECT DISTINCT my_data FROM my_table
WHERE my_data REGEXP ('[0-9]');
Substitute the value "my_data" with your field name and "my_table" with your table name.
Construct your query using the above as an example of how to determine whether a field is numeric or not.
-
-
3
Execute your query on some test data first. Check that the output matches what you expect to happen before using the query on live data.
-
1
Tips & Warnings
If using a Microsoft database system, you can substitute the regular express in the above example with the IsNumeric function, a more efficient way to perform the same operation.
Always check your SQL queries carefully before executing them. Mistakes can result in data loss and other unintended consequences.
Differences exist between database systems. Always check the documentation for your particular database when working with queries.
