Search code examples
sqlsql-serverdata-masking

How to change letter SQL Server?

I change word letters (5 AND 6 letter changing) and some letter masking. But I take error change letter and I don't do masking.

My query:

SELECT
    NAME,
    CONCAT(SUBSTRING(NAME, 1, 4),
           SUBSTRING (NAME, 6, 1),
           SUBSTRING(NAME, 5, 1),
           SUBSTRING(NAME, 7, LEN(NAME) -5))
FROM
    INFORMATION

This error is

Invalid length parameter passed to the LEFT or SUBSTRING function.

How can I do? (SQL Server 2014.)

Solution

  • The problem you have is that you're passing -5 to length of the column "NAME" which is less than 5 and the function SUBSTRING can't accept negative values. If you want to avoid that you can always add ABS to you expression, however, you need to verify that it is returning what you are expecting:

    SELECT
    NAME,
    CONCAT(SUBSTRING(NAME, 1, 4),
           SUBSTRING(NAME, 6, 1),
           SUBSTRING(NAME, 5, 1),
           SUBSTRING(NAME, 7, ABS(LEN(NAME) -5)))
FROM
    INFORMATION