Some time nested function will not work, what is the solution? !!!!!!
Want to process each character of string???
--drop function dbo.replacechars
create FUNCTION replacechars (@InStr varchar(max))
RETURNS varchar(max)
AS
BEGIN
declare @alter_string varchar(max) , @length int , @cnt int , @StoreChar varchar(100)
SET @alter_string = ''
SET @length = 0
SET @cnt = 1
SET @length = LEN(@InStr)
IF @length > 0
BEGIN WHILE @cnt <= @length
BEGIN
SET @StoreChar = SUBSTRING(@InStr, @cnt, 1)
set @StoreChar =
case when isnull(@StoreChar,' ') = ';' then '&#.x3b;'
when isnull(@StoreChar,' ') = '#' then '&#.x23;'
when isnull(@StoreChar,' ') = '&' then '&#.x26;'
when isnull(@StoreChar,' ') = '!' then '&#.x21;'
when isnull(@StoreChar,' ') = '@' then '&#.x40;'
when isnull(@StoreChar,' ') = '$' then '&#.x24;'
when isnull(@StoreChar,' ') = '%' then '&#.x25;'
when isnull(@StoreChar,' ') = '^' then '&#.x5e;'
when isnull(@StoreChar,' ') = '*' then '&#.x2a;'
when isnull(@StoreChar,' ') = '(' then '&#.x28;'
when isnull(@StoreChar,' ') = ')' then '&#.x29;'
when isnull(@StoreChar,' ') = '=' then '&#.x3d;'
when isnull(@StoreChar,' ') = '+' then '&#.x2b;'
when isnull(@StoreChar,' ') = '{' then '&#.x7b;'
when isnull(@StoreChar,' ') = '}' then '&#.x7d;'
when isnull(@StoreChar,' ') = '[' then '&#.x5b;'
when isnull(@StoreChar,' ') = ']' then '&#.x5d;'
when isnull(@StoreChar,' ') = '\' then '&#.x5c;'
when isnull(@StoreChar,' ') = '|' then '&#.x7c;'
when isnull(@StoreChar,' ') = '''' then '&#.x27;'
when isnull(@StoreChar,' ') = ':' then '&#.x3a;'
when isnull(@StoreChar,' ') = '"' then '&#.x22;'
when isnull(@StoreChar,' ') = '<' then '&#.x3c;'
when isnull(@StoreChar,' ') = '>' then '&#.x3e;'
when isnull(@StoreChar,' ') = '?' then '&#.x3f;'
when isnull(@StoreChar,' ') = '/' then '&#.x2f;'
when isnull(@StoreChar,' ') = CHAR(160) then '&#.xa0;'
when isnull(@StoreChar,' ') = CHAR(10) then '&#.xd;&#.xa;'
else isnull(@StoreChar,' ') end
BEGIN SET @alter_string = @alter_string + isnull(@StoreChar,' ') END
SET @cnt = @cnt + 1
END END
RETURN @alter_string END
Note - PLEASE remove . (dot) from string written after then clause
select dbo.replacechars('a ; f5637# $ % ^ ')
