none
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




    Sunday, January 20, 2013 3:09 PM

Answers

  • 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


    Sunday, January 20, 2013 3:50 PM
  • 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  

    Monday, January 21, 2013 3:41 PM

All replies

  • 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


    Sunday, January 20, 2013 3:50 PM
  • 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)


    • Edited by Aalamjeet Rangi Monday, January 21, 2013 3:22 AM Updated description about special characters and numbers
    Monday, January 21, 2013 3:17 AM
  • 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  

    Monday, January 21, 2013 3:41 PM
  • 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

    Monday, January 21, 2013 7:31 PM
  • Thanks Naomi. The code was taken from another sample (removing non-number) function. I modified the earlier code.
    Monday, January 21, 2013 10:01 PM