SQL trying to replace middle characters with *-CodePudding

I am trying to replace SQL results with all the middle values with asterix, *. All results are words. I am using SSMS. The words that are 4-5 letters, it should only show 1 letter in the beginning, one to the end. 6 letters and more, it it should only show 2 letter in the beginning, 2 letters in the end. 1-3 letters, no replacement.

For example: (I am now using - instead of * so it does not make the text bold).

"Banana" 6 letters should become ba--na

"False" 5 letters should become F---e

"a" stays the same

"Selin is a vegetable and banana is a fruit" becomes "S---n is a ve-----le and ba--na is a f---t."

What I have done so far, is to make this for emails, after the @. But now I want it to happen with every word of the result.

What I've done:

DECLARE @String VARCHAR(100) = '[email protected]'

SELECT STUFF(STUFF(@STring,
                   CHARINDEX('@',@String) 2,
                   (CHARINDEX('.',@String, CHARINDEX('@',@String))-CHARINDEX('@',@String)-3),
                   REPLICATE('*',CHARINDEX('.',@String, CHARINDEX('@',@String))-CHARINDEX('@',@String)))
               ,2
               ,CHARINDEX('@',@String)-3
               ,REPLICATE('*',CHARINDEX('@',@String)-3))```

With result [email protected]

instead of -

And I tried the mask method

Select 
--select first character from Email and use replicate
SUBSTRING(Sxolia,1,1)   REPLICATE('*',5)  
--function to put asterisks
SUBSTRING(Sxolia,CHARINDEX('@',Sxolia),len(Sxolia)-CHARINDEX('@',Sxolia) 1) 
--at this statement i select this part  @gmail,com and to first part to become like this A*****@gmail.com
as Emailmask
From [mytable]

With result B***** Bana is a fruit

And

declare @str nvarchar(max)
select @str = '123456'

select '****'   substring(@str, 5, len(@str) - 3)

Result: ****56

Not what I am looking for.

How should I look into this?

CodePudding user response：

I'm not sure how e-mail fits into all this because you're asking for word masks, so I'm going to assume you actually want this. Use divide and conquer to implement this, so first implement an expression that would do this for simplest cases (e.g. single words). Then if you need it for e-mails, just split the e-mails however you see fit and then apply the same expression.

The expression itself is rather simple:

SELECT * 
  FROM (VALUES 
      ('banana'),
      ('selin'),
      ('vegetable')
     ) words(word)
 CROSS
 APPLY (SELECT CASE 
                 WHEN ln BETWEEN 4 AND 5
                 THEN LEFT(word, 1)   REPLICATE('*', ln-2)   RIGHT(word, 1)
         
                 WHEN ln >= 6
                 THEN LEFT(word, 2)   REPLICATE('*', ln-4)   RIGHT(word, 2)
         
                 ELSE word
               END as result
          FROM (VALUES (LEN(words.word))) x(ln)
    ) calc

This already provides the expected result. You could define a function out of this, if you have the permissions, and use it like so:

SELECT * 
  FROM (VALUES 
      ('banana'),
      ('selin'),
      ('vegetable')
     ) words(word)
 CROSS
 APPLY fnMaskWord(word)