Separating letters and numbers from strings in SQL Server

Separating letters and numbers from strings in SQL Server

by

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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

1
2
3
4
5
6
7
8
9
10
11
12
/* 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

Separating letters and numbers from strings in SQL Server

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

Related reads

Reference

  • 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.