How to identify rows containing alphabetic characters in SQL column

Data and Databases
1

I’m working with a database table that has a column which should only store numeric values, but some entries accidentally include letters. I need to write a SQL query to filter out records that contain any alphabetic characters mixed with numbers.

Here’s my sample data:

Contact_Info
-----------
01.45.78.92
01.67.89.34 ABCDE
078965432
87654321 MNOP

I want to extract only these rows:

01.67.89.34 ABCDE
87654321 MNOP

I’ve attempted using where Contact_Info like '%[A-Z]%' and where Isnumeric(Contact_Info) = 0 but I’m not getting the expected results. What would be the correct approach to identify entries that contain both numbers and letters?

2

what database are u using? the approaches mentioned work great, but for mysql you could also try WHERE Contact_Info RLIKE '[0-9].*[A-Za-z]|[A-Za-z].*[0-9]' - it catches mixed patterns in either order. have u tested with different char encodings or special chars?

3

Indeed, ISNUMERIC is not the solution for this scenario as it considers various non-numeric inputs. Instead, using regular expressions would be more effective. If you’re working with MySQL, you can apply: WHERE Contact_Info REGEXP '[0-9]' AND Contact_Info REGEXP '[A-Za-z]'. This effectively checks for the presence of both numeric and alphabetic characters within the same entry. For SQL Server, the query would be: WHERE Contact_Info LIKE '%[0-9]%' AND Contact_Info LIKE '%[A-Za-z]%', ensuring that only rows with mixed content are returned, while purely numeric and purely alphabetic entries are excluded.

4

try WHERE Contact_Info ~ '[0-9]' AND Contact_Info ~ '[a-zA-Z]' if ur on PostgreSQL. this’ll catch rows with both digits and letters mixed together. the ~ operator’s perfect for this kind of pattern matching.