Question: How to Extract Alphanumeric Only From A String?
Answer: One of my blog readers sent a mail
I have a table. In one of the columns, I want to extract the values which have both alphabets and numbers. I want to find out such a pattern only and exclude other values. I tried many methods and nothing seemed working. Can you please help me?”
Well. On reading this mail, I immediately thought of using a pattern matching technique using Regular expression.
Let us create this data set
CREATE TABLE #TEST (COL VARCHAR(20)) INSERT INTO #TEST SELECT '90982' AS DATA UNION ALL SELECT 'SQL SERVER' UNION ALL SELECT 'CA100TEST' UNION ALL SELECT '0.10002' UNION ALL SELECT '@#%#$^'
Now use the following code and execute
SELECT COL FROM #TEST WHERE COL NOT LIKE '%[^0-9A-Z]%' AND COL LIKE '%[A-Z]%' AND COL LIKE '%[0-9]%'
The result is
The logic is that the condition COL NOT LIKE ‘%[^0-9A-Z]%’ will exclude any values with no alphabets and numbers. The next two conditions COL LIKE ‘%[A-Z]%’ AND COL LIKE ‘%[0-9]%’ will make sure that the returned value has both alphabets as well as numbers
This is one of the methods I can think of. If you know any simpler method, let me know in the comments. I will publish your method with due credit to you. Here are a few of the previous interview questions:
- What is Consolidation of Index? – Interview Question of the Week #212
- Does Parallel Threads Process Equal Rows? – Interview Question of the Week #211
- What is Trusted Constraint in SQL Server? – Interview Question of the Week #210
Reference: Pinal Dave (https://blog.sqlauthority.com)