Regular expression to validate whether the data contains numeric ( or empty is also valid)

Question

Hi all,

i have to validate the data contains numeric or not and if it is not numeric return 0 and if it is numeric or empty return 1.

Below is my query i tried in SQL.

SELECT dbo.Regex('^[0-9]','123') -- This is returning 1.

SELECT dbo.Regex('^[0-9]','') -- this is not returning 1 but i want to return as 1 and i try to put space in "pattern" also it is not working...

please can any one help....

Thanks in advance

Answer 1

Try:

SELECT dbo.Regex('^[0-9]*$','123')

or better yet:

SELECT dbo.Regex('^\d*$','123')

\d is standard shorthand for [0-9]. Note that when you use [0-9] it means "match exactly one digit. The * wildcard means match zero or more so \d* means match zero or more digits, which includes an empty result.

Answer 2

Try ^[0-9]*$, which will match 0 or more digits only:

SELECT dbo.Regex('^[0-9]*$','')

should return 1, as should...

SELECT dbo.Regex('^[0-9]*$','123')

Answer 3

Your Regex is not quite right: "^[0-9]" checks only, if the first character in the string is a number. Your regex would return 1 on "3abcde".

Use

"^\d*$"

to check if the field contains only numbers (or nothing). If you want to allow whitespace, use

"^\s*\d*\s*$"

If you want to allow signs and decimals, try

"^\s*(+|-)?\d*\.?\d*\s*$"