Find letters, numbers and special characters from a column in sql server

Suppose we have a table in that a column having mix dates (I mean it has letters, number and special character s). We need find out.

Suppose having the below data

DECLARE @table table (id int identity(1,1), value nvarchar(100)) INSERT INTO @table VALUES ('B65bfNM#*amF5*t'), ('4GZ_3h^2STJTZVG') , ('4%bKU&hxgj7!3!t'), ('uX=g3PE+w&uVV!h')

With the help of below sql script we will get it

DECLARE @table table (id int identity(1,1), value nvarchar(100)) INSERT INTO @table VALUES ('B65bfNM#*amF5*t'), ('4GZ_3h^2STJTZVG') , ('4%bKU&hxgj7!3!t'), ('uX=g3PE+w&uVV!h') SELECT ID,value,     (SELECT         SUBSTRING(value, number, 1)     FROM master..spt_values     WHERE SUBSTRING(value, number, 1) LIKE '[A-Z]' AND type = 'P'     FOR xml PATH ('')) as [Letters],                 (SELECT         SUBSTRING(value, number, 1)     FROM master..spt_values     WHERE SUBSTRING(value, number, 1) LIKE '[0-9]' AND type = 'P'     FOR xml PATH ('')) as [Numbers],                 (SELECT         SUBSTRING(value, number, 1)     FROM master..spt_values     WHERE SUBSTRING(value, number, 1) LIKE '[^0-9]'     AND SUBSTRING(value, number, 1) LIKE '[^A-Z]' AND type = 'P'     FOR xml PATH ('')) as [special characters] FROM @table

See the output