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? — Codeman
– Codeman, Commented Aug 19, 2014 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
– Andreas, Commented Aug 19, 2014 at 16:51

Tom · Accepted Answer · 2014-08-19 16:52:09Z

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, 2014 at 16:52

answered Aug 19, 2014 at 16:47

Tom

7,7401 gold badge26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jt2ouan Over a year ago

This works perfectly. Have never heard of the Replicate function. Thank you!

Bohdan Over a year ago

Hi @Tom - what is the way if I need to replace all characters except spaces ?

Tak · Accepted Answer · 2014-08-19 16:48:39Z

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, 2014 at 16:48

Tak

1,5921 gold badge9 silver badges9 bronze badges

1 Comment

Jt2ouan Over a year ago

Thanks. Had to give the 'check' to the one who answered it first.

Codeman · Accepted Answer · 2014-08-19 16:43:54Z

-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, 2014 at 16:43

Codeman

12.4k10 gold badges57 silver badges92 bronze badges

Collectives™ on Stack Overflow

SQL Replace all characters in a string

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related