Sunday, February 14, 2016

Function to process each character of String [SQL server]

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# $ % ^ ')

No comments:

Post a Comment

web stats