Replace all the characters in a string by specific character

• MVIT

  Right there with Babe

  Points: 748

  More actions

  May 30, 2014 at 1:14 am

  #289318

  How to replace all the characters in a string by specific character.

  e.g. my string is '123123123'

  the output I want is '#########'

  i.e. replace every string based on its length with # characters.

  the number of characters in my string '123123123' is 9

  so I need an output with having 9 # i.e. '#########'

• dbalmf

  SSCrazy

  Points: 2063

  More actions

  May 30, 2014 at 2:33 am

  #1717685

  Hi there,

  If it is a straight like for like replacement then this would work

  update [mytable]

  set [mycolumn] = '#########'

  where len([mycolumn]) = 9

  If the task is more complex then you will need to be using REPLACE or STUFF or SUBSTRING with CHARINDEX or any number of other methods.

  To get better help read the link in my signature which discribes the method to post information.

  ============================================================
  David

  http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

• BrainDonor

  SSCoach

  Points: 19254

  More actions

  May 30, 2014 at 3:00 am

  #1717689

  This uses REPLICATE to replace each character in a string with '#':

  CREATE TABLE #test(

  TestvalueVARCHAR(60)

  );

  INSERT INTO #test(Testvalue)

  VALUES('one'),

  ('long string'),

  ('slightly longer string than the previous one'),

  ('12121212');

  SELECT * FROM #test;

  UPDATE #test

  SET Testvalue = REPLICATE('#',LEN(Testvalue));

  SELECT *

  FROM #test;

  DROP TABLE #test;

  Steve Hall
  Linkedin
  Blog Site