-3

I have a variable declared as nvarchar in the SP, its value is dynamic, it can be a string or a number, its value comes from the UI when user writes in the global search textbox to search a table. I need to cast its value from a string to a decimal only when the value is a number, however, I'm getting error when casting because the cast() returns error when casting a string like 'abc' but works fine if the value is '2000'.

How to check if the value is castable or not?

SELECT CAST('abc' AS DECIMAL(7,2) ) -- retruns error Error converting data type varchar to numeric.

SELECT CAST('2.0000' AS DECIMAL(7,2) ) -- retruns 2.00

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
Ali
  • 1,633
  • 7
  • 35
  • 58

1 Answers1

2

You tagged this mysql, but MySQL does not raise an error when you try to cast a non-numeric string to a number. It returns 0 in that case (which I consider a design flaw in this DBMS).

The error message you are showing suggests another DBMS, most likely Microsoft SQL Server.

In SQL Server use TRY_CAST, which returns null if the string is not numeric:

SELECT *
FROM mytable
WHERE numcol = TRY_CAST(@searchstring AS DECIMAL(7,2));

The above query returns no rows if the string contains a non-numeric value. If you want to return all rows instead:

SELECT *
FROM mytable
WHERE numcol = TRY_CAST(@searchstring AS DECIMAL(7,2))
OR TRY_CAST(@searchstring AS DECIMAL(7,2)) IS NULL;

Docs: https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver16

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73