How to Extract Alphanumeric Only From A String? – Interview Question of the Week #214

5 years ago

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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

Data Type, SQL Scripts, SQL Server, SQL String

How to Find Table Cardinality from the Execution Plan? – Interview Question of the Week #213

What is Clean Buffer in DBCC DROPCLEANBUFFERS? – Interview Question of the Week #215

5 Comments. Leave new

Bora KAŞMER
February 27, 2019 11:32 am
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]%’
Reply
Macnr
March 1, 2019 2:34 am
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]%’
Reply
- Eric
  March 1, 2019 10:20 pm
  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.
  Reply
Eric
March 1, 2019 10:28 pm
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.
Reply
Deb Warren
March 19, 2021 8:37 am
Is there a way I can do this in Excel??
Reply