Separate numbers, letters and special characters from string

How to find and separate the numbers, letters and special characters from string?

Suppose you a column in table which contains a mix words which may includes numbers, letters, and special characters, or either numbers and letters only , or letters and special characters only that could be a any combination of them.

Now you are asked to find and separate the letters, number and special characters from string into columns.

To demonstrate that, first you need to create a sample table which contains a mix words (Numbers, letters and special characters)  as shown below.

CREATE TABLE dbo.Datatable
(
RowId INT IDENTITY(1,1),
MixWords NVARCHAR(250)
)

INSERT INTO dbo.Datatable (MixWords)
VALUES 
('ABCDE12345@#^&'), 
('67890FGHIJ#$&)('),
('-+QA$%&10876NMBCH'), 
('ABC*&%09875VS#%@'),
('ABCDSFE0987432'),
('3432544*(&*#$#'),
('KJGDLFF*(&*#$#')

Now you have a table named DataTable , and the column MixWords having a mix words means it has letters, numbers and special characters.

SELECT * FROM dbo.Datatable

Lets implement the solution for requirement, first we will use Recursive CTE for generating a sequence starting from 1 to 4000.

Note that, the last number of sequence should not be less than the max length of string, as these sequence number you will allow you to read each character in string in looping.

So in case, if the max length of any string value is 100 and you have generated a sequence numbers till 50 only then it be able to read and find only for 50 characters from string.

Lets generate a sequence number using up to 4000 and store them in temporary table for further use.

IF OBJECT_ID(N'tempdb..#NumberList') IS NOT NULL
BEGIN
DROP TABLE #NumberList
END;

WITH CTE
AS (
SELECT 1 AS n
UNION ALL 
SELECT n + 1
FROM CTE WHERE n <= 3999
)

SELECT n INTO #NumberList
FROM CTE OPTION (MAXRECURSION 3999);

As you can see, it generates sequence up to 4000.




Lets quickly verify the temporary table #NumberList.

As you can see, it is having a number from 1 to 4000.

Lets separate the letters, numbers and special characters into columns.

SELECT RowId, MixWords, (
SELECT SUBSTRING(MixWords, n, 1) FROM
#NumberList 
WHERE SUBSTRING(MixWords, n, 1) LIKE '[0-9]' 
FOR xml PATH ('') 
) AS Numbers,
(
SELECT SUBSTRING(MixWords, n, 1) FROM
#NumberList 
WHERE SUBSTRING(MixWords, n, 1) LIKE '[A-Z]' 
FOR xml PATH ('') 
)AS Letters,
(
SELECT SUBSTRING(MixWords, n, 1) FROM
#NumberList 
WHERE SUBSTRING(MixWords, n, 1) LIKE '[^0-9]' 
AND SUBSTRING(MixWords, n, 1) LIKE '[^A-Z]' 
FOR xml PATH ('') 
)AS SpecialCharacters

FROM dbo.Datatable;

As you can see, now Numbers, letters, and special characters are separated into columns.