Menu
declare @input varchar(255) = 'abc'
select * from table where id = CAST(@input as int)
Can I do this so that the cast will fail silently, or default to some user-provided (or system default) value?
823
CAST() If the cast fails, the TRY_CAST() function returns NULL while the CAST() function raises an error. You use the NULL handling functions or expressions such as ISNULL() , COALESCE , or CASE to handle the result of the TRY_CAST() function in case the cast fails.
TRY_CAST takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CAST returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CAST fails with an error.
LPAD() function in MySQL is used to pad or add a string to the left side of the original string. The actual string which is to be padded. If the length of the original string is larger than the len parameter, this function removes the overfloating characters from string.
From SQL Server 2012 and up you can use try_convert and try_parse functions.
Until then you can use
DECLARE @input VARCHAR(11) = 'abc'
SELECT *
FROM table
WHERE id = CAST(CASE WHEN @input NOT LIKE '%[^0-9]%' THEN @input END AS INT)
You may need to tweak the test a bit (e.g. it disallows negative integers and allows positive ones bigger than the maximum int) but if you find a suitable test (e.g. the one here) and use a CASE statement you should avoid any casting errors as order of evaluation for CASE is mostly guaranteed.
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
