SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create Function RemoveNumbers(@Temp VarChar(8000))
Returns VarChar(8000)
AS
Begin
    Declare @FindNum as varchar(8) = '%[0-9].%' --Pretty sure this is just a literal
    Declare @ExtraDigits as varchar(8) = '%[0-9]@%'

    While PatIndex(@FindNum, @Temp) != 0
        Set @Temp = Stuff(@Temp, PatIndex(@FindNum, @Temp), 2, '@')

    While PatIndex(@ExtraDigits, @Temp) != 0
        Set @Temp = Stuff(@Temp, PatIndex(@ExtraDigits, @Temp), 2, '@')

    Return @Temp
End

Declare @TestValue VARCHAR(8000) = '1.    aaaaaaaaaaaaaaaaaaaaaaaaa.2.    bbbbbbbbbbbbbbbbbbbbbbbbbbbb.3.    cccccccccccccccccccccccccccccccc.4.    dddddddddddddddddddddddddddddddd.5.    adfafasdfasf.[1][1].'

Select
    @TestValue As TestValue,
    dbo.RemoveNumbers(@TestValue) As RemoveNumbersResult

Select S.*, Trim(S.value) As TrimmedValue
From String_Split(dbo.RemoveNumbers(@TestValue), '@', 1) S

Select S.*, Trim(S.value) As TrimmedValue, S.ordinal - 1 AS AdjustedOrdinal
From String_Split(dbo.RemoveNumbers(@TestValue), '@', 1) S
WHERE S.ordinal >= 2 OR S.value <> ''



TestValue
RemoveNumbersResult


1.    aaaaaaaaaaaaaaaaaaaaaaaaa.2.    bbbbbbbbbbbbbbbbbbbbbbbbbbbb.3.    cccccccccccccccccccccccccccccccc.4.    dddddddddddddddddddddddddddddddd.5.    adfafasdfasf.[1][1].
@    aaaaaaaaaaaaaaaaaaaaaaaaa.@    bbbbbbbbbbbbbbbbbbbbbbbbbbbb.@    cccccccccccccccccccccccccccccccc.@    dddddddddddddddddddddddddddddddd.@    adfafasdfasf.[1][1].




value
ordinal
TrimmedValue



1


    aaaaaaaaaaaaaaaaaaaaaaaaa.
2
aaaaaaaaaaaaaaaaaaaaaaaaa.

    bbbbbbbbbbbbbbbbbbbbbbbbbbbb.
3
bbbbbbbbbbbbbbbbbbbbbbbbbbbb.

    cccccccccccccccccccccccccccccccc.
4
cccccccccccccccccccccccccccccccc.

    dddddddddddddddddddddddddddddddd.
5
dddddddddddddddddddddddddddddddd.

    adfafasdfasf.[1][1].
6
adfafasdfasf.[1][1].




value
ordinal
TrimmedValue
AdjustedOrdinal


    aaaaaaaaaaaaaaaaaaaaaaaaa.
2
aaaaaaaaaaaaaaaaaaaaaaaaa.
1

    bbbbbbbbbbbbbbbbbbbbbbbbbbbb.
3
bbbbbbbbbbbbbbbbbbbbbbbbbbbb.
2

    cccccccccccccccccccccccccccccccc.
4
cccccccccccccccccccccccccccccccc.
3

    dddddddddddddddddddddddddddddddd.
5
dddddddddddddddddddddddddddddddd.
4

    adfafasdfasf.[1][1].
6
adfafasdfasf.[1][1].
5


Declare @TestValue VARCHAR(8000) = '1. aaa.2. bbb.10.xxx.11.   yyy.99999.zzz.'

Select
    @TestValue As TestValue,
    dbo.RemoveNumbers(@TestValue) As RemoveNumbersResult

Select S.*, Trim(S.value) As TrimmedValue, S.ordinal - 1 AS AdjustedOrdinal
From String_Split(dbo.RemoveNumbers(@TestValue), '@', 1) S
WHERE S.ordinal >= 2 OR S.value <> ''



TestValue
RemoveNumbersResult


1. aaa.2. bbb.10.xxx.11.   yyy.99999.zzz.
@ aaa.@ bbb.@xxx.@   yyy.@zzz.




value
ordinal
TrimmedValue
AdjustedOrdinal


 aaa.
2
aaa.
1

 bbb.
3
bbb.
2

xxx.
4
xxx.
3

   yyy.
5
yyy.
4

zzz.
6
zzz.
5

value	ordinal	TrimmedValue
	1
aaaaaaaaaaaaaaaaaaaaaaaaa.	2	aaaaaaaaaaaaaaaaaaaaaaaaa.
bbbbbbbbbbbbbbbbbbbbbbbbbbbb.	3	bbbbbbbbbbbbbbbbbbbbbbbbbbbb.
cccccccccccccccccccccccccccccccc.	4	cccccccccccccccccccccccccccccccc.
dddddddddddddddddddddddddddddddd.	5	dddddddddddddddddddddddddddddddd.
adfafasdfasf.[1][1].	6	adfafasdfasf.[1][1].