I was trying to mask the string by using STUFF function in SQL.

DECLARE @String VARCHAR(20)='TEST12346',
        @Start    INT = 3,
        @Length   INT = 3;

SELECT @String AS MyString,
       STUFF(@String,  @Start,  @Length, REPLICATE('*', @Length))

It only works with strings length longer than 5, else it will return NULL.

How to handle the following case?

Expected Result

CodePudding user response：

Regarding the iif(LEN(@String) > @Length 2, @Length, LEN(@String) - 2 ) :

If for example the length of string is 3 THEN REPLICATE param should be LEN(@String) - 2 = 1 not 3

And if length of string is higher than 5 then REPLICATE param should be 3

DECLARE @String VARCHAR(20)='TESTII',
        @Start    INT = 3,
        @Length   INT = 3;

SELECT @String AS MyString,
       iif(LEN(@String) >= 3,
         STUFF(@String,  @Start ,  @Length, REPLICATE( '*', iif(LEN(@String) > @Length   2, @Length, LEN(@String) - 2 ) ) ),
         STUFF(@String,  @Start - 1,  0, REPLICATE( '*', 0  ) )
       )

CodePudding user response：

You could build the entire String as if it was long enough for all the '*'-characters. Then wrap the result in a LEFT-function and only return the desired output.

SELECT LEFT( SUBSTRING(String, 0, @Start)
             REPLICATE('*',@Length)
             SUBSTRING(String, @Start @Length, LEN(@String))
           , LEN(String)) 
  FROM (VALUES ('TE'), ('TES'), ('TEST'), ('TESTI'), ('TESTIN'), ('TEST12346'))t(String)