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:

D code

set col:myURL value: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:

D code

set col:companyName value: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

D code

derive type:single value:SUBSTITUTE(string_source,string_pattern,replacement_string)


ArgumentRequired?Data TypeDescription
string_sourceYstringName of the column, a function returning a string, or string literal to be applied to the function
string_patternYstringString literal or pattern to find
string_replacementYstringString literal to use as replacement
pattern_beforeNstringString literal or pattern to find before finding the string_pattern value.
pattern_afterNstringString literal or pattern to find after finding the string_pattern value.

...

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

...