get only alphanumeric rows in sql?

sql, sql-server, sql-server-2008

Solution

You could use `PATINDEX`:

DECLARE @test table(col varchar(30));
INSERT INTO @test
   SELECT * FROM (SELECT 'CLARK' UNION ALL SELECT 'KING' UNION ALL SELECT 'MILLER' UNION ALL SELECT 'SMITH, $800 .00' UNION ALL SELECT 'JONES, $2975.00' UNION ALL SELECT 'SCOTT, $3000.00' UNION ALL SELECT 'ADAMS, $1100.00' UNION ALL SELECT 'FORD, $3000.00' UNION ALL SELECT 'ALLEN30' UNION ALL SELECT 'WARD30' UNION ALL SELECT 'MARTIN30' UNION ALL SELECT 'BLAKE30' UNION ALL SELECT 'TURNER30' UNION ALL SELECT 'JAMES30')AS T(Spalte)

`Patindex` returns the starting position of the first occurrence of a pattern in a specified expression. Since i've used a pattern that matches any non-alphanumeric (^ Wildcard - Character), this returns only records which contain only alphanumeric characters (+ white-spaces):

SELECT col 
FROM   @test
WHERE  PATINDEX('%[^a-zA-Z0-9 ]%',col) = 0

Result:

CLARK
KING
MILLER
ALLEN30
WARD30
MARTIN30
BLAKE30
TURNER30
JAMES30

Problem

I've column with data ``` CLARK KING MILLER SMITH, $800 .00 JONES, $2975.00 SCOTT, $3000.00 ADAMS, $1100.00 FORD, $3000.00 ALLEN30 WARD30 MARTIN30 BLAKE30 TURNER30 JAMES30 ``` I'want only alphanumeric rows means ``` SMITH, $800 .00 JONES, $2975.00 SCOTT, $3000.00 ADAMS, $1100.00 FORD, $3000.00 ``` except this rows all rows should be included? How can i get this?

Original source