Remove non-numeric or non-alphameric characters.

This can be used to remove or save any range of characters from a string or field.

declare @s varchar(100), @i int

select @s = 'asd i/.,<>as>[{}]vnbv'
select @s

select @i = patindex('%[^a-zA-Z0-9 ]%', @s)
while @i > 0
begin
select @s = replace(@s, substring(@s, @i, 1), '')
select @i = patindex('%[^a-zA-Z0-9 ]%', @s)
end

select @s


gives
before
asd i/.,<>as>[{}]vnbv
after
asd iasvnbv


Removing the characters from a field in a table


create table #a (s varchar(100))

insert #a (s) select 'asd i/.,<>as>[{}]vnbv'
insert #a (s) select 'aaa'
insert #a (s) select '123 ''h 9)'

select * from #a

while @@rowcount > 0
update  #a
set s = replace(s, substring(s, patindex('%[^a-zA-Z0-9 ]%', s), 1), '')
where patindex('%[^a-zA-Z0-9 ]%', s) <> 0

select * from #a

Gives

before
asd i/.,<>as>[{}]vnbv
aaa
123 'h 9)
after
asd iasvnbv
aaa
123 h 9