How do I replace all characters in a string with an asterisk, except for spaces?


Hello I'm not very good at SQL so any help would be great thank you.

I have a table with 2 columns word and replacement

right now my replacement column is null however i'm trying to do a update statement which counts the character from the world column then adds specific symbol to the replacement column

for example value in word column is Hello World the update would add

Hello World, ***** *****

right now i got simple

update Words
set replacement = Replace(words, '*')

Here's a function you can use to perform such update.

It uses a number table, you can read more here if you dont yet have one (create one!).

create function dbo.ReplaceChars(@Word varchar(max), @Char char(1))
returns varchar(max)

    declare @output varchar(1000);
    set @output = replicate(@Char, len(@Word));

    select  @output = stuff(@output, n, 1, ' ')
    from    (   select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union
                select 7 union select 8 union select 9 union select 10 union select 11 union select 12
            ) number(n) --** use your own number table here **
    where   substring(@Word, n, 1) = ' ' and
            n <=len(@Word);

   return @output;


declare @table table (Word varchar(max), Replacement varchar(max))
insert into @table (Word)
    select 'Hello World' union all
    select 'one two yak';

update  @table
set     Replacement = dbo.ReplaceChars(Word, '*')
where   Word is not null;

select * from @table;