CODEWITHSUNDEEP
sql-server-2008-r2isnumeric
Gloria Santin
Gloria Santin

Reputation: 2136

ISNUMERIC() does not work for decimals

I am using the SQL Server 2008 R2 function ISNUMERIC() to determine if a string is a decimal or not. It is returning '1' when the decimal as a comma or comma and period. I can fix for those issues but this query is part of a SSRS Report that will be distributed among many users and I cannot correct of all problems.

What can I use instead of ISNUMERIC() to determine if the string is a correctly formed decimal? I cannot use TRY_PARSE() because that is available in SQL SERVER 2012 and I am on 2008 R2.

UPDATE I want to replace ISNUMERIC() with PATINDEX() to identify decimals. Both of these examples return 0

SELECT PATINDEX('9,0', '%[0-9]%.%[0-9]%')
SELECT PATINDEX('9.0', '%[0-9]%.%[0-9]%')

What am I doing wrong?

Upvotes: 0

Views: 1682

Answers (1)

Isaac
Isaac

Reputation: 3363

Your parameter values for PATINDEX are reverse. This should work for you...

SELECT PATINDEX('%[0-9]%,%[0-9]%', '9,0')
SELECT PATINDEX('%[0-9]%.%[0-9]%', '9.0')

Noel

Upvotes: 1

Related Questions