Have strange DB developed which has no keys and allows entry of numbers into varchar which also allows nulls

Question

How can I ensure that TSQL will not bark at me with these values returned:

'1.00000000' or NULL or '' or 'some value'

When i convert to an int

Answer 1

If you are using SQL Server 2012 or later, you may use the TRY_CONVERT function, e.g.

WITH yourTable AS (
    SELECT 123 AS intVal UNION ALL
    SELECT '123' UNION ALL
    SELECT NULL
)

SELECT
    intVal,
    CASE WHEN TRY_CONVERT(int, intVal) IS NOT NULL THEN 'yes' ELSE 'no' END AS can_parse
FROM yourTable;

Demo

The TRY_CONVERT function will return NULL in this case if it can't convert the input to an integer. So, this is a safe way to probe your data before trying a formal cast or conversion.

Answer 2

Here was the answer I found that worked for me... TSQL - Cast string to integer or return default value I'm not on 2012 or higher due to customer...

Don't give me credit though :) I was only good at searching for the answer that worked for me...

Although I changed it from returning null to returning zero since the stupid varchar should be an int column with a default of zero :)

Here's one that works for any value that is truly a VARCHAR and not an int since VARCHAR is really a variable length string data type

WITH tmpTable AS (
SELECT '123' as intVal UNION ALL
SELECT 'dog' UNION ALL
SELECT '345' UNION ALL
SELECT 'cat' UNION ALL
SELECT '987' UNION ALL
SELECT '4f7g7' UNION ALL
SELECT NULL

)

SELECT
intVal
,case when intVal not like '%[^0-9]%' then 'yes' else 'no' end FROM tmpTable;

credit given to Tim Biegeleisen for his answer above.... All though when characters are found with his solution it will still error out... hence the changes

Demo