Separating letters and numbers from strings in SQL Server

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)
	IF @strInput IS NULL

		@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)

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');


Separating letters and numbers from strings in SQL Server

Hope this will be helpful for someone for separating letters and numbers.

Related reads


  • Read more about the TSQL string function PATINDEX at Microsoft Docs.

Leave your thoughts...

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