How to fetch alphanumeric string from table

Assume that you have a table which contains alphabetic, numeric, alphanumeric string, and special characters and you are asked to fetch only alphanumeric string from those strings.

Following is the sample table named Numbers which contains alphabetic, numeric, alphanumeric and special characters strings.

CREATE TABLE dbo.Numbers
(
Id INT, Val VARCHAR(15)
)

INSERT dbo.Numbers
(Id, Val)
VALUES
(1, 'ABCXDE101'),
(2, '03256263'),
(3, 'BSFSGS'),
(4, '2432VS64'),
(5, '35435683'),
(6, 'IFSNDS'),
(7, 'ABNTY'),
(8, '848427')

There are many possible ways using T-SQL to fetch only alphanumeric string values from table, Lets take a look.

Using PATINDEX function

Following T-SQL, uses PATINDEX function which returns the starting position of the first occurrence of a pattern in a column Val.

SELECT Id, Val FROM dbo.Numbers
WHERE PATINDEX('%[^0-9A-Z]%', Val) = 0 
AND PATINDEX('%[A-Z]%', Val) > 0 
AND PATINDEX('%[0-9]%', Val) > 0

Lets understand the condition that we have specified in where conditions.

PATINDEX(‘%[^0-9A-Z]%’, Val) =0, it ensures that string should not contain any special characters/non -alphanumeric characters.

PATINDEX(‘%[A-Z]%’, Val) > 0, it ensures that string should contain alphanumeric characters.

PATINDEX(‘%[0-9]%’, Val) >, it ensures that string should contain numeric characters.

Lets see the output of T-SQL, you can see it returns only alphanumeric string only.

 

Using LIKE clause

Following T-SQL, uses LIKE clause, which matches a value to a similar values using wildcard operators.

SELECT Id, Val FROM dbo.Numbers
WHERE Val NOT LIKE '%[^0-9A-Z]%'
AND Val LIKE '%[A-Z]%' 
AND Val LIKE '%[0-9]%'

Lets understand the condition that we have specified in where conditions.

Val NOT LIKE ‘%[^0-9A-Z]%’, it ensures that string should not contain any special characters/non -alphanumeric characters.

Val LIKE ‘%[A-Z]%’, it ensures that string should contain alphanumeric characters.

Val LIKE ‘%[0-9]%’, it ensures that string should contain numeric characters.

Lets see the output of T-SQL, you can see it returns only alphanumeric string only.

Also Read..

LIKE clause

PATINDEX function

SQL Server Interview Q & A

 20,586 total views,  7 views today