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.
12,165 total views, 5 views today
I tried above query with out N replace some values it doesn’t work how could i do without N..?