I am attempting to use the TRANSLATE function in SQL Server. However, I am receiving an error stating that the second and third arguments of the TRANSLATE function must contain an equal number of characters.

Query

CREATE TABLE employee 
(
    EmployeeID INT,                
    FullName VARCHAR(100)          -- Column for Full Name (string)
);

INSERT INTO employee (EmployeeID, FullName) 
VALUES (1, 'Stewie Griffin'),
       (2, 'Peter B Griffin'),
       (3, 'Lois Griffin'),
       (4, 'Brian O''Connor'),
       (5, 'Meg Griffin');

SELECT 
    TRANSLATE(FULLNAME, 
              SUBSTRING(FULLNAME, 2, CHARINDEX(' ', FULLNAME) - 1), 
              REPLICATE('*', LEN(SUBSTRING(FULLNAME, 2, CHARINDEX(' ', FULLNAME) - 1))))
FROM
    employee

Error:

The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

Expected Behavior

I expect the query to replace each character in the specified substring with an asterisk (*) because as I have checked, the second and third arguments seem to be having the same length as each other.

I am attempting to use the TRANSLATE function in SQL Server. However, I am receiving an error stating that the second and third arguments of the TRANSLATE function must contain an equal number of characters.

Query

CREATE TABLE employee 
(
    EmployeeID INT,                
    FullName VARCHAR(100)          -- Column for Full Name (string)
);

INSERT INTO employee (EmployeeID, FullName) 
VALUES (1, 'Stewie Griffin'),
       (2, 'Peter B Griffin'),
       (3, 'Lois Griffin'),
       (4, 'Brian O''Connor'),
       (5, 'Meg Griffin');

SELECT 
    TRANSLATE(FULLNAME, 
              SUBSTRING(FULLNAME, 2, CHARINDEX(' ', FULLNAME) - 1), 
              REPLICATE('*', LEN(SUBSTRING(FULLNAME, 2, CHARINDEX(' ', FULLNAME) - 1))))
FROM
    employee

Error:

The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

Expected Behavior

I expect the query to replace each character in the specified substring with an asterisk (*) because as I have checked, the second and third arguments seem to be having the same length as each other.

• sql
• sql-server

• 2 Your problem is the expression SUBSTRING(FULLNAME, 2, CHARINDEX(' ', FULLNAME) - 1) actually includes the space at the back. And len() does not count the traillling space. Either change to -2 to exclude the trailing space or use DATALENGTH() – Squirrel Commented Mar 4 at 11:43
• 1 What are you actually trying to achieve here? Some kind of masking? Why not use data masking? – Thom A Commented Mar 4 at 11:48
• @Squirrel very insightful. solved my problem. thanks. – cydar2500 Commented Mar 4 at 11:53
• @ThomA How to actually do it? could you please share your code? – cydar2500 Commented Mar 4 at 11:54
• 2 Dynamic data masking – Thom A Commented Mar 4 at 11:55

1 Answer 1

TRANSLATE replaces all occurrences of the selected chars, as you can see for Brian O'Connor who becomes B**** O'Co**or: by including the n (of Brian) in TRANSLATE(), you ask it to replace all ns.

You probably want to replace the found substring instead of any characters found in the substring:
simply cut & paste together: the first letter, the *s, the last name from the space.

SELECT
  SUBSTRING(FULLNAME, 1, 1)
  +REPLICATE('*', CHARINDEX(' ', FULLNAME) - 2)
  +SUBSTRING(FULLNAME, CHARINDEX(' ', FULLNAME), LEN(FULLNAME) - CHARINDEX(' ', FULLNAME) + 1)
FROM employee;

(see it in a fiddle)