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
