P Kernel P Kernel - 1 year ago 85
SQL Question

SQL Server Query Output with sql scenario replace values with 'x'

I have one col in my table like:



I want my output something like this:



iyara
replaced with 5x's(xxxxx) and 'kira' with 4x's(xxxx)

Answer Source

There's probably a better way to do it that this, but this achieves what you're after:

DECLARE @email NVARCHAR(50) = '[email protected]';

SELECT  LEFT(@email, 1) + REPLICATE('X',
                                    LEN(SUBSTRING(@email, 2,
                                                  CHARINDEX('@', @email) - 1)))
        + SUBSTRING(@email, CHARINDEX('@', @email), LEN(@email));

-- Result: [email protected]

This uses the REPLICATE() method, which according to the docs online starts with SQL Server 2008.

REPLICATE (Transact-SQL)

Repeats a string value a specified number of times.

REPLICATE ( string_expression ,integer_expression )

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download