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

Question

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, '*')

Can you elaborate? Do you have a list of replacements, e.g. "st" -> "street", "hello" -> "aloha" etc? Or just filters, e.g. "world", "hello", "test" all to stars? — RichardTheKiwi, May 15 '13 at 21:51
no list just would like to replace the character with sql asterisk like hello you would be ***** *** — Neo, May 15 '13 at 22:00
So you want to replace each letter with an asterisk, with the exception of spaces? — Gordon Linoff, May 15 '13 at 22:02

score 1 · Accepted Answer · edited May 23 '17 at 11:43

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)
as
begin

    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;

end



--usage
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;

vvvv4d · Answer 2 · 2013-05-20T02:15:40.167

Using the replicate function you can easily accomplish exactly what you are trying to do.

replicate('*',len(word))

Example:

declare @test table (
     word varchar(100)
    ,replacement varchar(100)
)

insert into @test
values
 ('bad',null)
,('word',null)

select
     word test1
    ,replacement test1
from @test

update @test
set replacement = replicate('*',len(word))
where replacement is null

select
     word test2
    ,replacement test2
from @test

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

2 Answers2