January 31, 2014 at 3:50 pm
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
January 31, 2014 at 4:17 pm
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.
January 31, 2014 at 5:23 pm
Thanks a lot Luis/SSCrazy. Your solution worked like magic :w00t:
January 31, 2014 at 5:29 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply