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
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