How to Verify If a String Contains Only Numbers in SQL

SQL (Structured Query Language) is a powerful tool used for managing relational databases, and it is often necessary to validate the integrity of the data stored within these databases. One common requirement is to check if a string contains only numeric values. This article will explore various methods to accomplish this using regular expressions (regex) in SQL.

Introduction to Regular Expressions in SQL

Regular expressions (regex) provide a powerful and flexible way to match patterns in strings. They are widely supported in modern SQL databases, including MySQL, PostgreSQL, SQL Server, and more. Regex can be used to check if a string adheres to a specific pattern, such as containing only numeric values.

Using REGEXP with SQL Databases

The `REGEXP` function allows you to perform regular expression-based pattern matching on strings. This function checks whether a given string matches a specified pattern. Here’s how you can use `REGEXP` to check if a string contains only numbers in different SQL databases.

MySQL Example

In MySQL, the `REGEXP` function can be used as follows:

select id from registry where desc REGEXP '^[0-9] $'

This query selects all `id` values from the `registry` table where the `desc` column contains only numbers. The `^[0-9] $` pattern breaks down as follows:

`^` asserts the start of the string. `[0-9] ` matches one or more digits (numbers). `$` asserts the end of the string.

This ensures that the entire string is composed of digits and nothing else.

PostgreSQL Example

PostgreSQL also supports the `~` operator for regex matching. Here’s the equivalent query in PostgreSQL:

select id from registry where desc ~ '^[0-9] $'

This query works similarly to the MySQL version, using the same pattern to check that the `desc` column contains only numbers.

SQL Server Example

SQL Server uses the `LIKE` operator in combination with the wildcard characters for regex-like matching. Here’s how you can achieve the desired result:

SELECT [id] FROM [registry] WHERE [desc] LIKE '[0-9]%' AND [desc] NOT LIKE '%[^0-9]%'

This query is a bit more complex due to the lack of a direct `REGEXP` function in SQL Server. It checks that the string starts with a digit and contains no non-digit characters.

Exploring Other Validation Techniques in SQL

While regular expressions are powerful, there are alternative methods to validate strings in SQL. Depending on the database system, you might also consider using built-in functions or custom functions to check for numeric values. For instance, in SQL Server, you can use the `TRY_CAST` or `TRY_CONVERT` functions to detect non-numeric strings.

Here’s an example in SQL Server:

SELECT [id] FROM [registry] WHERE TRY_CAST([desc] AS INT) IS NOT NULL

This query attempts to convert the `desc` value to an integer. If the conversion fails (indicating that the string contains non-numeric characters), the condition evaluates to false.

Conclusion

Verifying if a string contains only numbers is a critical task in database management and data validation. Regular expressions provide a versatile and efficient way to perform this check using SQL. Whether you are working with MySQL, PostgreSQL, SQL Server, or another database system, understanding how to use regex can significantly enhance your ability to maintain and manage your database data.