Masking characters in varchar

0

Please login or register to vote for this query.

(click on this box to dismiss)

Theoretical Computer Science

Q&A for theoretical computer scientists and researchers in related fields

CREATE TABLE #tab(col VARCHAR(100));

INSERT INTO #tab
VALUES ('CVS'),('Walmart'),('Exxon Mobil'), ('Green Mountain Coffee');

SELECT col, 
masked = 
    coalesce(
    -- handle 2 spaces
    STUFF(STUFF(
     STUFF(col, 3, LEN(col)-4, REPLICATE('*',LEN(col)-4)),
    CHARINDEX(' ', col), 1, ' '),
    CHARINDEX(' ', col, CHARINDEX(' ', col)+1), 1, ' '),
    -- handle 1 spaces
    STUFF(
     STUFF(col, 3, LEN(col)-4, REPLICATE('*',LEN(col)-4)),
    CHARINDEX(' ', col), 1, ' '),
    -- normal masking
    STUFF(col, 3, LEN(col)-4, REPLICATE('*',LEN(col)-4)),
    -- too short to mask
    col
    )
FROM #tab;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results