Create below function
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
now run this query
with a as
(
select 'GP100' as no1
union all
select 'GPS120'
)
Select no1, dbo.RemoveNonAlphaCharacters(no1) alphabets,replace(no1,dbo.RemoveNonAlphaCharacters(no1),'') nos from a
Happy Coding!
:)