Unanswered: Determine if char/varchar col has int data?
I need a way to determine if a char/varchar column has data that could be converted to an int.
I have a couple of consolidation tables that includes a column for userId. Most of the time this data is strictly numeric (even though it is a varchar(50) column). I would like to convert that column value to an int and then lookup a name in a user table based on the int value as a key, but since that column contains some non-numeric data I get a syntax error when I run my query.
Well since there was no response, I will post my solution so that it might be of some help to others.
When I tried to use the convert(int, key_col) on my char(50) defined column in a query, I received an error because some of the data in key_col was not all numeric. I was using this key_col column to join to a lookup table that had a numeric primary key column. So I simply went the other way with the conversion.
Instead of trying to convert the key_col to a numeric value I converted the numeric (int) column to a char(50) and did the join. This approach worked great because it not only gave me the join that I wanted but it also filtered out the non-numeric records in my main table.