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

Question: How to Extract Alphanumeric Only From A String?

Answer: One of my blog readers sent a mail

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

“Hi Pinal,

Solarwinds

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

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

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)

Solarwinds
, , ,
Previous Post
How to Find Table Cardinality from the Execution Plan? – Interview Question of the Week #213
Next Post
What is Clean Buffer in DBCC DROPCLEANBUFFERS? – Interview Question of the Week #215

Related Posts

4 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]%’

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

Leave a Reply

Menu