home




Function to return all non-alphameric characters from a string in a table.
Author Nigel Rivett
This function will take a string and return a table giving the position of the characters and there ascii code for all non alphameric characters.

create function GetCtrl
(@str varchar(1000))
returns @t table (offset int, chr int)
as
begin
declare @i int, @j int

	select @i = 1, @j = 1
	while @i <= len(@str)
	begin
		select @j = @i
		select @i = patindex('%[^a-zA-Z0-9 ]%', substring(@str,@i,len(@str)-@i+1))
		if @i = 0 
			set @i = len(@str) + 10
		else
		begin
			select @i = @i + @j -1
			insert @t select @i, ascii(substring(@str,@i,1))
		end

		select @i = @i + 1

	end
return
end

select * from dbo.GetCtrl ('sad%sa*s(sdfg*')

i           chr         
----------- ----------- 
4           37
7           42
9           40
14          42



home