SQL Replace all characters in a string

Question

Is there any SQL statements to replace everything in a string with an 'X'. The strings aren't all the same length so it makes it a bit tricky. I haven't been able to find anything that does this except the function below but it takes a long time when I pass in 'a-z0-9' since I have to search on all of those but I really just want to replace everything no matter what it is.

[dbo].[cfn_StripCharacters]
 (
    @String NVARCHAR(MAX), 
 @MatchExpression VARCHAR(255)='a-z0-9'
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression =  '%['+@MatchExpression+']%'

WHILE PatIndex(@MatchExpression, @String) > 0
    SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, 'X')

RETURN @String

For example the data column looks like this and I want to replace the whole string with x's:

975g -> XXXX

ryth5 -> XXXXX

1234vvsdf5 -> XXXXXXXXXX

test1234 -> XXXXXXXX

possible duplicate of [Regex pattern inside SQL Replace function?](http://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function) — Codeman, Aug 19 '14 at 16:44
If your goal is to obfuscate a field, why do you care the length of the string? Can you just `update tableName set protectedInfoColumn = 'XXX';`? — Andreas, Aug 19 '14 at 16:51

Tom · Accepted Answer · 2014-08-19T16:52:09.433

8

If this is SQL Server, you can use the REPLICATE function and simply replicate x the LEN() of the string.

SELECT REPLICATE('x', LEN(@String))

sqlFiddle

Edit - Looks like this is also available in MySQL via REPEAT() but I haven't tested

edited Aug 19 '14 at 16:52

answered Aug 19 '14 at 16:47

Tom

7,640
1
23
47

This works perfectly. Have never heard of the Replicate function. Thank you! – Jt2ouan Aug 19 '14 at 17:46
Hi @Tom - what is the way if I need to replace all characters except spaces ? – Bohdan Nov 28 '21 at 18:09

score 0 · Answer 2 · answered Aug 19 '14 at 16:48

0

You can use Replicate and Len functions to replace all characters.

declare @str varchar(20)

set @str = 'abc'

select @str = REPLICATE('X', len(@str))

select @str

answered Aug 19 '14 at 16:48

Tak

1,561
1
9
8

Thanks. Had to give the 'check' to the one who answered it first. – Jt2ouan Aug 19 '14 at 17:47

score -1 · Answer 3 · answered Aug 19 '14 at 16:43

-1

There are no built in functions to do it. You can write a CLR function that does a regex replace, though.

answered Aug 19 '14 at 16:43

Codeman

12,157
10
53
91

SQL Replace all characters in a string

3 Answers3