Replace part of a string with "XXXXXXX"

[Dan-RSM]

I have a file that some rows start with a sensitive number that we want to obscure the middle part of that number. All the rows are in STRING format and only one field. This is RAW data that we want to obscure before any other processing. I have been able to locate and identify any row that has the data we want to obscure. Here is what we want to do...

Current (THIS IS FAKE DATA)

Field_1

1231235672953867      more text:      medical condition Cost:0000123.00

billed:010122        paid:010222    partialpaymentindicator:N

outstanding:0000000.00

672953867123123       more text:      medical condition Cost:0000123.00

billed:010122        paid:010222    partialpaymentindicator:N

outstanding:0000000.00

5672912312353867      more text:      medical condition Cost:0000123.00

billed:010122        paid:010222    partialpaymentindicator:N

outstanding:0000000.00

I need it to be converted to...

Field_1

1231235xxxxxx867      more text:      medical condition Cost:0000123.00

billed:010122        paid:010222    partialpaymentindicator:N

outstanding:0000000.00

6729538xxxxxx23       more text:      medical condition Cost:0000123.00

billed:010122        paid:010222    partialpaymentindicator:N

outstanding:0000000.00

5672912xxxxxx867      more text:      medical condition Cost:0000123.00

billed:010122        paid:010222    partialpaymentindicator:N

outstanding:0000000.00

This is what I have, and while it does identify the correct rows

IF (regex_match(LEFT([Field_1],36),"^\d{16}\s{10}\d{4}\s\d{4}\s") OR regex_match(LEFT([Field_1],36),"^\d{15}\s{11}\d{4}\s\d{4}\s")) THEN REGEX_Replace(LEFT([Field_1],7), "^\d{6}", "xxxxxx") ELSE [Field_1] ENDIF

This replaces the full line with just...

xxxxxx8

xxxxxx2

xxxxxx8

I need the line to stay intact except for the "X's" 

I have searched the online community and help and not found how to do this.

Thank you for any help

-Dan

[gautiergodard · Accepted solution]

Hello @Dan-RSM 

This is not the prettiest of solutions but if should work based on the sample you provided.

Please mark this thread as resolved if this provided the answer to your question.

Good luck!

[Dan-RSM · Accepted solution]

I also am trying this, but it is throwing a "Malformed Function Call" error

IF (regex_match(LEFT([Field_1],36),"^\d{16}\s{10}\d{4}\s\d{4}\s") OR regex_match(LEFT([Field_1],36),"^\d{15}\s{11}\d{4}\s\d{4}\s")) THEN REGEX_Replace(Left([Field_1],5)+"XXXXXXX"+(Right([Field_1],4)) ELSE [Field_1] ENDIF

[Dan-RSM]

Thanks, I will try this now

[Dan-RSM]

That got me there. Thanks!!!