How to fetch alphanumeric string from table

Assume you have a table containing alphabetic, numeric, alphanumeric strings, and special characters, and you need to retrieve only the alphanumeric strings from that data.

Below is a sample table named Numbers that includes alphabetic, numeric, alphanumeric strings, and special characters.

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

INSERT dbo.Numbers
(Id, Val)

VALUES
(1, 'ABCXDE101'),
(2, '03256263'),
(3, 'BSFSGS'),
(4, '35435683'),
(5, '2432VS64'),
(6, 'IFSNDS'),
(7, 'ABNTY'),
(8, '848427'),
(9, '1@#AB427'),
(10, '%^@')

There are several ways to use T-SQL to retrieve only alphanumeric string values from a table. Let’s explore a few of them.

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

Let’s examine the conditions specified in the WHERE clause.

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.

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

 

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]%'

Let’s examine the conditions specified in the WHERE clause.

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.

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

Also Read..

LIKE clause

PATINDEX function

SQL Server Interview Q & A