sql - Need Better Solution to remove special characters and Numbers -

sql - Need Better Solution to remove special characters and Numbers -

is there improve way remove special characters , numbers in column, not limited 1 or 2 remove special characters , numbers. of i using query (jst logic part). allow me know there improve selection table contains minimum 5 1000000 records

declare @name varchar(1000) = '231323lig%$%$h$%t' declare @dumy varchar(1000) while(patindex('%[0-9]%',@name)<>0) set @name = stuff(@name,patindex('%[0-9]%',@name),1,'') while(patindex('%[a-z]%',@name)<>0) begin set @dumy = isnull(@dumy,'')+substring(@name,patindex('%[a-z]%',@name),1) set @name = stuff(@name,patindex('%[a-z]%',@name),1,'') end set @name = @dumy select @name 'clean'

try function:

create function [dbo].[removenonalphacharacters](@temp varchar(1000)) returns varchar(1000) begin declare @keepvalues varchar(50) set @keepvalues = '%[^a-z]%' while patindex(@keepvalues, @temp) > 0 set @temp = stuff(@temp, patindex(@keepvalues, @temp), 1, '') homecoming @temp end

call this:

select dbo.removenonalphacharacters('231323lig%$%$h$%t')

output:

clean lite

update

if want entire table seek this

select dbo.removenonalphacharacters(columnname),othercolumn1,othercolumn2 table1

sql sql-server sql-server-2008