Home » Separate numbers, letters and special characters from string

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.




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.