Just do the update on rows
where column2 like '[0-9]%'
[Edit - OP has attempted to clarify what they meant]
Try something like
select dbo.RemoveNonAlphaCharacters(column2) from ex
where you have a function like this
create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^a-z ]%'
While PatIndex(@KeepValues, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
Return @Temp
End
which I adapted from the solution by G Mastros on
this post[
^]