Removing NonAlphaNumeric Characters

[ter79]

I'm sure that this has been asked before but I can't find anything. I need to be able to delete nonalphanumeric characters from multiple columns in a single table, how can I do this

 

[NoCoolHandle]

UPdate table set col1 = replace(col1,'a','b'),col2 = replace(col2,'1','')


or something like that...


(is it a specific char or just non numeric? what are you looking for)

 

[ter79]

Anything that is not a letter or number is to be removed

 

[NoCoolHandle]

There is no easy function that will allow you to do this in SQL. You could write some kind of statement that would look for an instance of a non alphnum and then do a replace on that then find another non alpha and keep doing that till they are all gone, but that seems to be something that should be getting done in the application.

You might want to look at dts...

 

[clapag22]

declare @y varchar(100)

select @y = '12099023434ghfkjlhjAADF++++&&@#(*$<>>>"":''''MKFND'

while patindex('%[^0-Z]%',@y) > 0
	begin
	select @y = substring(@y, 1, patindex('%[^0-Z]%',@y) - 1) + substring(@y, patindex('%[^0-Z]%',@y) + 1, len(@y))
	end

select @y

 

[ESquared]

Interesting. I would have though that the pattern match '[0-Z]' would match the characters :;<=>?@ which are between 9 and A in the Ascii character set, but it doesn't. You can use '[:-@]' as its own small range. Oh, I see: the alphanumeric characters (including accented ones, depending on collation settings) and nonalphanumeric characters are separate sets that can't be mixed in a range. Starting and ending with characters from the same 'set' does work: '[ -÷]' matches all characters between space (#32) and divided (#247) that aren't a number or a letter.


-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[ESquared]

Forgot to post the code if you want to play with it for yourself.

create table #t (
		num int,
		ch char(1)
	)
declare @c int
	set @c = 0
while @c<256 begin
	insert into #t
		select @c, Char(@c)
	set @c=@c+1
end
select *
	from #t
	where ch like '[ -÷]'
drop table #t

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.