Long time back, I wrote an article about extracting numbers for a given string in SQL Server. Today I came across another requirement where I had to separate both letters and numbers for the given string. So, I wrote this small user defined function to segregate letters and numbers.
The below udf uses a loop-based approach to extract letters and numbers from a given input string variable. Using the PATINDEX function along with WHILE loops and the STUFF function, I have progressively removed the characters and numbers that match specific patterns.
Function for separating letters and numbers
CREATE FUNCTION [dbo].[mtb_SegregateTextAndNumbers]
(
@strInput VARCHAR(max)
)
RETURNS @Result Table
(
Input VARCHAR(max),
Alpha VARCHAR(max),
Number VARCHAR(max)
)
AS
BEGIN
IF @strInput IS NULL
RETURN;
DECLARE
@strAlpha VARCHAR(max) = @strInput,
@strNumber VARCHAR(max) = @strInput,
@numRange as VARCHAR(10) = '%[0-9]%',
@alpRange as VARCHAR(10) = '%[^0-9]%'
/* Get Letters */
WHILE PatIndex(@NumRange, @strAlpha) > 0
SET @strAlpha = Stuff(@strAlpha, PatIndex(@NumRange, @strAlpha), 1, '')
/* get Numbers */
WHILE PatIndex(@alpRange, @strNumber) > 0
SET @strNumber = Stuff(@strNumber, PatIndex(@alpRange, @strNumber), 1, '')
INSERT INTO @Result SELECT @strInput, ISNULL(@strAlpha,0), ISNULL(@strNumber,0)
RETURN
END
GO
Testing the function
/* Testing the function */
SELECT * from dbo.mtb_SegregateTextAndNumbers('1a2b3c');
SELECT * from dbo.mtb_SegregateTextAndNumbers('123abc');
SELECT * from dbo.mtb_SegregateTextAndNumbers('abc123');
SELECT * from dbo.mtb_SegregateTextAndNumbers('a123b');
SELECT * from dbo.mtb_SegregateTextAndNumbers('1abc2');
SELECT * from dbo.mtb_SegregateTextAndNumbers('123abc123abc');
SELECT * from dbo.mtb_SegregateTextAndNumbers('');
SELECT * from dbo.mtb_SegregateTextAndNumbers('NonNumeric');
SELECT * from dbo.mtb_SegregateTextAndNumbers(NULL);
SELECT * from dbo.mtb_SegregateTextAndNumbers('Azure4234SQL232Data333Warehouse23and0Parallel,Data43434Warehouse');
GO
Result
Hope this will be helpful for someone for separating letters and numbers.
Related reads
- How to extract numbers from a string in SQL Server?
- How to extract numbers from a string In Python?
Reference
- Read more about the TSQL string function PATINDEX at Microsoft Docs.