Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

...

Column reference example:

SUBSTITUTE
d-codelang-syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextset col:myURL value:
substitute(myURL,`{ip-address}`,myDomain)

substitute(myURL,`{ip-address}`,myDomain)

Output: Searches the myURL column values for sub-strings that match valid IP addresses. Where matches are found, they are replaced with the corresponding value in the myDomain column. 

Function reference example:

SUBSTITUTE(UPPER
d-code-lang-syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextset col:companyName value:
substitute(upper(companyName),'ACME','New ACME')

substitute(upper(companyName),'ACME','New ACME')

Output: Searches the uppercase version of values from the companyName column for the string literal ACME. When found, these matches are replaced by New ACME in the companyName column.

D s
snippetSyntax

SUBSTITUTE
d-code-lang-syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextderive type:single value:
substitute(string_source,string_pattern,replacement_string)

substitute(string_source,string_pattern,replacement_string)

...

For security purposes, you wish to redact the first three sets of digits, so only the last set of digits appears. 

TransformTransformation:

To make the substitution, you must first change the type of the column to be a string:

d-

...

trans
RawWrangletrue
Typestep
WrangleTextsettype col: CreditCardNum type: 'String'
p01NameColumns
p01ValueCreditCardNum
p02NameNew type
p02Value'String'
SearchTermChange column data type

You can then use the following transform transformation to perform the pattern-based replacement of four-digit sets that end in a dash with XXXX:

d-

...

trans
RawWrangletrue
Typestep
WrangleTextset col: CreditCardNum value: substitute(CreditCardNum, `{digit}+\-`, 'XXXX-')
p01NameColumns
p01ValueCreditCardNum
p02NameFormula
p02Valuesubstitute(CreditCardNum, `{digit}+\-`, 'XXXX-')
SearchTermEdit column with formula

To indicate that the column no longer contains valid information, you might choose to rename it like in the following:

d-

...

trans
RawWrangletrue
p03Value'CreditCardNumOBSCURED'
Typestep
WrangleTextrename mapping: [CreditCardNum,'CreditCardNumOBSCURED']
p01NameOption
p01ValueManual rename
p02NameColumn
p02ValueCreditCardNum
p03NameNew column name
SearchTermRename columns

Results:

TransactionIdCreditCardNumOBSCUREDAmtDollars
T001XXXX-XXXX-XXXX-1111100.29
T002XXXX-XXXX-XXXX-0004510.21
T003XXXX-XXXX-XXXX-009162.13
T004XXXX-XXXX-XXXX-04294.12

...