Replace non-alpha characters only from the beginning of a string

Question

How to remove the non-alphabetic characters from the beginning of a string in SQL Server.

ex: 6Hai should return Hai and H6ai should return H6ai

I am using SQL Server 2017.

Answer 1

A pattern could be used to replace non-alphabetic characters from a string as below:

Declare @Temp as varchar(50)
set @Temp = '6Hai'
Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) = 1
        Set @Temp = Stuff(@Temp, 1, 1, '')

select @Temp

Or a function could be defined as :

Create Function [dbo].[RemoveNonAlphaCharactersFromStart](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) = 1
        Set @Temp = Stuff(@Temp, 1, 1, '')

    Return @Temp
End

and invoke the function as :

Select dbo.RemoveNonAlphaCharactersFromStart('6Hai')

Refer this answer to remove all non-alpha characters : https://stackoverflow.com/a/1008566/7804477

I have updated the function such that only the non-alphabetic characters from the beginning are removed:

Stuff and PatIndex are built-in functions.

This is the crucial condition to check only at the beginning of the string: While PatIndex(@KeepValues, @Temp) = 1

Stuff(@Temp, 1, 1, '') - this replaces the first character in the string with an empty character ''. (the second argument is the starting position and the third argument denotes the number of characters to replace)