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