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

Question: How to Extract Alphanumeric Only From A String?

“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)

## SQL SERVER – Puzzle – Why does sp_spaceused Show No Values?

• 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.