Modify String Values

You can edit values in a column by applying one of the available string functions. The following transformation can be modified for any of the available string functions:

myCol

MyStringFunction($col)

For more information see String Functions.

When transforming strings, a key step is to trim off the whitespace at the beginning and ending of the string. For the above dataset, you can use the following command to remove these whitespaces:

All

TRIM($col)

The above transform uses the following special values, which are available for some transforms like set:

*

$col

The previewed data looks like the following, in which five strings are modified and now match the base string:

Figure: Trim data to improve matches

The platform language, Wrangle, provides presets to identify missing or mismatched values in a selection of data.

Missing values preset: The following transform replaces missing URL values with the text string http://www.example.com. The preset ISMISSING([Primary_WebSite_or_URL]) identifies the rows missing data in the specified column:

Primary_Website_or_URL

IF(ISMISSING([Primary_Website_or_URL]),'http://www.example.com',$col)

For more information, see Find Missing Data.

Mismatched values preset: This transform converts to 00000 all values in the Zip column that are mismatched against the Zipcode data type. In this case, the preset ISMISMATCHED(Zip, ['Zipcode']) identifies the mismatched values in the column, as compared to the Zipcode data type:

Zip

IF(ISMISMATCHED(Zip, ['Zipcode']),'00000',$col)

For more information, see Find Bad Data.

An entry in the example data contains an additional word: My String extra. You can use a simple replace command to remove it:

String

' extra'

''

true

The global parameter causes the replacement to be applied to all instances found within a cell value. Otherwise, the replacement occurs only on the first instance.

There are multiple ways of removing double spaces, or any pattern, from text values. For best results, you should limit this change to individual columns.

String

' '

''

true

CamelCase refers to text in which multiple words are joined together by removing the spaces between them. In the example data, the entry MyString is an example of CamelCase.

You can use Wrangle to break up CamelCase entries in a column of values. The following transforms use regular expressions to identify patterns in a set of values:

String

`({alpha})({upper})`

'$1 $2'

true

The first transform locates all instances of uppercase letters followed by lower-case letters. Each instance is replaced by a space, followed by the found string ($2). For more information, see Text Matching.

Remove last word:

For example, you need to remove the last word of a string and the space before it. You can use the following replace transform to do that:

String

` {alpha}+{end}`

''

true

When the above is previewed, however, you might notice that ending punctuation is not captured. For example, periods, exclamation points, and question marks at the end of your values are not captured in the Wrangle . To capture those values, the Find parameter must be expanded:

String

` {alpha}+({[?.!;\)]}|){end}`

''

true

In the second version, a capture group has been inserted in the middle of the on parameter value, as specified by the contents of the parentheses:

Reduce total number of words:

You need to cut each value in a column down to a maximum of two words. You can use the following to identify the first two words using capture groups in a Wrangle and then write that pattern back out, dropping the remainder of the column value:

String

`{start}({alpha}* )({alpha}*) ({any}*{end})`

'$1$2'

true

For the Find pattern:

You can trim out whitespace from an individual column via transformation. The TRIM function applied to string values removes the leading and trailing whitespace:

myCol

TRIM(myCol)

To apply this function across all columns in the dataset, you can use the following:

All

TRIM($col)

Notes:

In some cases, you may wish to remove all spaces, including those in between words or digits, in your strings:

All

REMOVEWHITESPACE($col)

If needed, you can remove all whitespace from a column of values.

name

Remove all whitespace

The following transformation removes all non-alphanumeric symbols from your string values, including:

All

Remove symbols

The following transformation converts all accented characters (e.g."ä") to unaccented characters (e.g "a").

All

Remove accents

When some files are imported into the application, leading and trailing quotes may remain for some or all columns. You can use the following transformation to remove these quotes from all columns:

All

Trim leading and trailing quotes

You can add fixed-string prefixes or suffixes to your string values. The following adds -0000 to a text version of the Zipcode column:

txtZipCode

Add suffix

'-0000'

You can use the following steps to set all text values in a column to be the same case.

Lower case:

myStrings

LOWER(myStrings)

Upper case:

myStrings

UPPER(myStrings)

Proper (sentence) case:

myStrings

PROPER(myStrings)

If you need all of your column values to be of the same length, one technique is to pad each string value at the front sufficiently, such that all string lengths in the column are identical.

This transformation results in adding enough spaces to each row value until the length of each value is 50 characters.

MyStrings

Pad with leading characters

' '

50

You can limit the maximum size of a column or set of columns to a fixed string length. For example:

col1,col2

IF(LENGTH($col)>32,LEFT($col,32),$col)

In the above, if the length of either column is longer than 32 characters, then the column value is set to the leftmost 32 characters. For shorter strings, the entire string is used.

For more information, see Manage String Lengths.