Replace all string except '$' with single character ' * '

Question

Hello all,

Is there anyway to replace all string with same character '*' except '$'?

Input :  $Hello$All$Please$Help$Me$

Output: $*****$***$******$****$**$

Thanks,

Sathish

Answer 1

Is there anyway to replace all string with same character '*' except '$'?

Input :  $Hello$All$Please$Help$Me$

Output: $*****$***$******$****$**$

One method:

DECLARE @input varchar(MAX) = '$Hello$All$Please$Help$Me$';
DECLARE @phrases xml;
SELECT @phrases = CONVERT(xml,'<input><phrase>' + REPLACE(@input,'$','</phrase><phrase>') + '</phrase></input>')

SELECT STUFF((
SELECT	'$' + REPLICATE('*', LEN(input.phrase.value('.','varchar(MAX)')))
FROM	@phrases.nodes('/input/phrase') input(phrase)
FOR XML PATH('')), 1, 1, '') AS MaskedInput;

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 2

Please see below to replace characters excluding special characters or numbers -

DECLARE @input varchar(MAX) = '$A$quick$Brown$foX$Jumps$ovEr$the$lazy$dog$!@#$%^&*()_+{}|[?]\:";<>,./$1234567890$';
DECLARE @string_replacement varchar(1) = '*'
PRINT 'BEFORE - ' + @input

WHILE @input LIKE '%[a-z]%'
    BEGIN
        SET @input = REPLACE(@input, SUBSTRING(@input,PATINDEX('%[a-z]%',@input),1), @string_replacement)
    END

PRINT 'AFTER  - ' + @input

BEFORE - $A$quick$Brown$foX$Jumps$ovEr$the$lazy$dog$!@#$%^&*()_+{}|[?]\:";<>,./$1234567890$
AFTER  - $*$*****$*****$***$*****$****$***$****$***$!@#$%^&*()_+{}|[?]\:";<>,./$1234567890$

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Answer 3

Declare @s varchar(100),@result varchar(100)

set @s=' $Hello$All$Please$Help$Me$'

set @result=''

SELECT @result=@result+

case when strs like '$' then strs else '*' end 
FROM
(
select substring(@s,number,1) as strs from

(
select number from master..spt_values
where type='p' and number between 1 and len(@s)

) as t1

) as t2

select @result  

Answer 4

In your implementation number chosen for the alias of the computed column is very confusing.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Thanks Naomi. The code was taken from another sample (removing non-number) function. I modified the earlier code.