Replace String with a special character except first character of word (like A***)

• ajay.contact

  SSC Veteran

  Points: 224

  More actions

  January 31, 2014 at 3:50 pm

  #396205

  Hi,

  I need help in replacing a string with another character (*) except the first one. Also it should keep the first chars of all words intact.

  Example:

  Apple=A****

  Red=R**

  Quick Brown Jump=Q**** B**** J***

  I am using below code, it works fine for one word. But it's not if there are multiple words in a string.

  Declare @FName varchar(100)

  SET @FName = 'Apple'

  Select Stuff (@FName, 2, len(@FName)-1, REPLICATE('*',len(@FName)-1))

  I would appreciate any help on this.

  Thanks

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 31, 2014 at 4:17 pm

  #1685251

  I'll leave you here 2 options based on solutions presented on a different thread for a ProperCase function found in here: http://www.sqlservercentral.com/Forums/Topic1531616-392-1.aspx

  The first solution uses the DelimitedSplit8K which is described over here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

  DECLARE @text varchar(8000) = 'Quick Brown Jump';

  SELECT STUFF((SELECT ' ' + LEFT(Item, 1) + REPLICATE( '*', LEN( Item) - 1)

  FROM dbo.DelimitedSplit8K( @text, ' ') s

  FOR XML PATH('')), 1, 1, '');

  with seed1 (a)

  as

  (

  select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all

  select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1

  ),

  numbers (n) as

  (

  select top (datalength(@text)) row_number() over (order by (select null))

  from seed1 s1, seed1 s2, seed1 s3

  )

  select a.b.value('(./text())[1]', 'varchar(8000)') as [text]

  from (

  select

  case

  when n = 1 then substring(@text, n, 1)

  WHEN substring(@text, n, 1) = ' ' THEN ' '

  when substring(@text, n - 1, 1) LIKE ' ' collate Latin1_General_CI_AI then substring(@text, n, 1)

  else '*'

  end

  from numbers

  for xml path (''), type

  ) a (b)

  Be sure to understand the code and feel free to ask any questions.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• ajay.contact

  SSC Veteran

  Points: 224

  More actions

  January 31, 2014 at 5:23 pm

  #1685259

  Thanks a lot Luis/SSCrazy. Your solution worked like magic :w00t:

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 31, 2014 at 5:29 pm

  #1685261

  ajay.contact (1/31/2014)

  ---

  Thanks a lot Luis/SSCrazy. Your solution worked like magic :w00t:

  I hope not, because you shouldn't know how magic works and you need to fully understand how the posted solutions work.:-D

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2