Question: How to Extract Alphanumeric Only From A String?
Answer: One of my blog readers sent a mail
“Hi Pinal,
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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
5 Comments. Leave new
This looks like better..
select * into #TEST from (
SELECT ‘90982’ AS Data UNION ALL
SELECT ‘SQL SERVER’ UNION ALL
SELECT ‘CA100TEST’ UNION ALL
SELECT ‘0.10002’ UNION ALL
SELECT ‘@#%#$^’) as tbl
select * FROM #TEST
SELECT Data FROM #TEST
WHERE Data NOT LIKE ‘%[^0-9A-Z]%’ AND Data LIKE ‘%[A-Z]%’ AND Data LIKE ‘%[0-9]%’
It is not working for below sample.
CREATE TABLE #TEST (COL VARCHAR(20))
INSERT INTO #TEST
SELECT ‘90982’ AS DATA UNION ALL
SELECT ‘1SQL SERVER’ UNION ALL
SELECT ‘CA100TEST’ UNION ALL– only return this one always.
SELECT ‘0.1A0002’ UNION ALL
SELECT ‘@#%#$4^’
SELECT COL FROM #TEST
WHERE COL NOT LIKE ‘%[^0-9A-Z]%’ AND COL LIKE ‘%[A-Z]%’ AND COL LIKE ‘%[0-9]%’
–Why not simple checking it has string and number together?
SELECT COL FROM #TEST
WHERE COL LIKE ‘%[A-Z]%’ AND COL LIKE ‘%[0-9]%’
The simplified WHERE COL LIKE ‘%[A-Z]%’ AND COL LIKE ‘%[0-9]%’ will return
1SQL SERVER
CA100TEST
0.1A0002
of which only CA100TEST is desired.
An alternative would be to check for the pattern existing/not-existing:
SELECT COL FROM #TEST
WHERE PATINDEX(‘%[A-Z]%’, COL) > 0 — Make sure it has ALPHA
AND PATINDEX(‘%[0-9]%’, COL) > 0 — Make sure it has NUMERIC
AND PATINDEX(‘%[^0-9A-Z]%’, COL) = 0 — Make sure it does not have non-Alphanumeric
Might be faster than two LIKEs and a NOT LIKE.
Is there a way I can do this in Excel??