I am trying to merge this with other similar fields which are in strings. If I change to integer then it would mess up the joins. I'm trying to keep this as strong. Thanks
If the values are known to contain non-numeric characters, then I absolutely agree. However, if they ARE supposed to contain only numerics, it's a great check using TRY_CONVERT()
I'd say it the other way: unless the values are all known to be only valid numeric chars, then treat it as a string. And if non-numeric chars are allowed in a column, some will get in somehow.
Why try to force a conversion to int here? It's not needed. What if the column is twenty+ chars long? You might not be able to convert to a (big)int anyway.
You missed what I was saying... if the column is SUPPOSED to be only numeric, then doing the convert to int will cause an error and you can take corrective action as a "bonus".
Actually I didn't miss that. I didn't state the explicitly, but that's just the wrong "solution".
If you need to check a column for numeric only, a trigger should automatically do that, you shouldn't have to coincidentally check it with every process that uses the column expecting an int value. That's not only wasteful, it risks wrong results, if someone later doesn't check it correctly.
Just use a CHECK constraint then. No need for a trigger.
If it is known that the column could have alpha or other non-numberics, you're code will also work fine. Consider replacing the LEN() function with an 8000... SQL knows when it has run out of characters.
Hmm, a CHECK would completely prevent the row from going in. Apparently such non-numeric values ARE allowed in the column, just not expected/preferred. Just as important, a CHECK could not remove the leading zeros if there were any, whereas a trigger could. And/or set another column to indicate whether the original value was a valid int or not.