# Strings functions

## Add padding

Adds one or more occurrence of a character at the beginning or the end of the content of the cells.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Repeat count | Number of times the chosen character will be added to the content of the cell. |
| Add character | Enter the character you want to add to the content of the cell as padding. |
| Position | Select where you want to add the padding character.<br><br><ul> <li> <p>Left: The chosen character will be added before the content of the cells.</p> </li> <li> <p>Right: The chosen character will be added after the content of the cells.</p> </li> </ul> |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

### Example

| Input | Configuration | Output |
| --- | --- | --- |
| TableRecipe | <ul> <li> <p>Repeat count: 5</p> </li> <li> <p>Add character 1</p> </li> <li> <p>Position: Left</p> </li> </ul> | 11111TableRecipe |

## Calculate length

Calculates the number of characters from each cell of the column and outputs the result in new column.

A new column containing the result of the function will be created for each of the selected column. The default name of the new columns will be `<original_column_name>_length`.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |

### Example

| Input | Output |
| --- | --- |
| TableRecipe | 11 |

## Change to lower case

Converts all the text from the column cells to lower case.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

### Example

| Input | Output |
| --- | --- |
| Table Recipe | table recipe |

## Change to title case

Converts all the text from the column cells to title case.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

### Example

| Input | Output |
| --- | --- |
| table recipe | Table Recipe |

## Change to upper case

Converts all the text from the column cells to upper case.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

### Example

| Input | Output |
| --- | --- |
| Table Recipe | TABLE RECIPE |

## Concatenate columns

Merges the content of a column with another one or a fixed value.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Prefix | Optionally add a character or string at the beginning of the merged data. |
| Use with | <ul> <li> <p><span>Value</span>: Select this option to concatenate the column content with a fixed value, set in the <span>Value</span> field.</p> </li> <li> <p><span>Other column</span>: Select this option to concatenate the column content with the content from another column, set in the <span>Column</span> drop-down list.</p> </li> </ul> |
| Separator | Input any character to act as separator for the merged data. |
| Add separator | Select the condition on which the separator will be applied. |
| Suffix | Optionally add a character or string at the end of the merged data. |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

### Example

| Input | Configuration | Output |
| --- | --- | --- |
| <ul> <li> <p>Column A: Table</p> </li> <li> <p>Column B:&nbsp;Recipe</p> </li> </ul> | <ul> <li> <p>Columns to process: A</p> </li> <li> <p>Prefix: a</p> </li> <li> <p>Use with: Other column</p> </li> <li> <p>Column: B</p> </li> <li> <p>Separator: @</p> </li> <li> <p>Add separator: Both values not empty</p> </li> <li> <p>Suffix: !</p> </li> </ul> | <ul> <li> <p>Column A: aTable@Recipe!</p> </li> <li> <p>Column B: Recipe</p> </li> </ul> |
| <ul> <li> <p>Column A: Qlik</p> </li> <li> <p>Column B: Cloud</p> </li> <li> <p>Column C: Analytics</p> </li> </ul> | <ul> <li> <p>Columns to process: A, B</p> </li> <li> <p>Prefix: #</p> </li> <li> <p>Use with: Other column</p> </li> <li> <p>Column: C</p> </li> <li> <p>Separator: +</p> </li> <li> <p>Add separator: Both values not empty</p> </li> <li> <p>Suffix: !</p> </li> </ul> | <ul> <li> <p>Column A: #Qlik+Analytics!</p> </li> <li> <p>Column&nbsp;B: #Cloud+Analytics!</p> </li> <li> <p>Column C: Analytics</p> </li> </ul> |

## Contains text

Checks if the cells contain a specific value. The function will return TRUE if the selected text is found within the cell, otherwise it will return FALSE. This function is case sensitive.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Use with | <ul> <li> <p><span>Value</span>: Select this option to check the column against a fixed value, set in the <span>Constant value</span> field.</p> </li> <li> <p><span>Column</span>: Select this option to check the column against values from another column, set in the <span>Other column</span> drop-down list.</p> </li> </ul> |

### Example

| Input | Configuration | Output |
| --- | --- | --- |
| <ul> <li> <p>Table Recipe</p> </li> <li> <p>Data Flow</p> </li> </ul> | <ul> <li> <p>Use with: Value</p> </li> <li> <p>Constant value: Recipe</p> </li> </ul> | <ul> <li> <p>TRUE</p> </li> <li> <p>FALSE</p> </li> </ul> |

## Convert to text

Converts the type of strings contained in the column so that they are interpreted as text in the system.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

## Extract a value by index

Extracts a substring selected using index positions, to a new column.

An index represents a number of characters from which or to which the selection applies. Index 0 corresponds to the first character.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| From | Select how you want to start the value selection:<br><br><ul> <li> <p><span>From beginning</span> to use the beginning of the string as starting point.</p> </li> <li> <p><span>From index</span> to specify the character number at which to start the selection, set in the following <span>Beginning index</span> field.</p> </li> <li> <p><span>From N before end</span> to specify how many characters from the end of the string to start the selection, set in the following <span>From N before</span> end field.</p> </li> </ul> |
| To | Select how you want to end the value selection:<br><br><ul> <li> <p><span>To end</span> to use the end of the string as ending point.</p> </li> <li> <p><span>To index</span> to specify the character number at which to end the selection, set in the following <span>End index</span> field.</p> </li> <li> <p><span>To N before end</span> to specify how many characters from the end of the string to end the selection, set in the following <span>To N before end</span> field.</p> </li> </ul> |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

## Hash data

Masks the information contained in a column. The input data is replaced using a 256 bit version of the Fowler-Noll-Vo algorithm.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

### Example

| Input | Output |
| --- | --- |
| Table recipe | G=HE!D)EV^\*H2%ZB-.(,I?.4+4J`Q%M]B&G_L5D0?2- |

### Example

| Input | Configuration | Output |
| --- | --- | --- |
| TableRecipe | <ul> <li> <p>From: From beginning</p> </li> <li> <p>To: To index</p> </li> <li> <p>End index: 5</p> </li> </ul> | Table |
| QlikCloudAnalytics | <ul> <li> <p>From: From index</p> </li> <li> <p>Beginning index: 4</p> </li> <li> <p>To: To index</p> </li> <li> <p>End index: 9</p> </li> </ul> | Cloud |

## Match pattern

Checks if the cell is similar to a specific pattern. The function will return TRUE if the selected pattern is found within the cell, otherwise it will return FALSE.

A new column containing the result of the function will be created for each of the selected column. The default name of the new columns will be `<original_column_name>_matches`.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Pattern | Select one of the predefined regex patterns, or another combination of operator and value:<br><br><ul> <li> <p>[a-z]+ (a word in lower case)</p> </li> <li> <p>[A-Z]+ (a word in upper case)</p> </li> <li> <p>[0-9]+ (number)</p> </li> <li> <p>[a-zA-Z]+ (a word , case insensitive)</p> </li> <li> <p>[a-zA-Z0-9]+ (any literal with alpha-numeric characters)</p> </li> <li> <p>Other</p> </li> </ul> |
| Operator | If you have selected Other as Pattern, you can select one of the following operator, and set you custom value in the following Value field:<br><br><ul> <li> <p>Equals</p> </li> <li> <p>Contains</p> </li> <li> <p>Starts with</p> </li> <li> <p>Ends with</p> </li> <li> <p>Regex</p> </li> </ul> |

### Example

| Input | Configuration | Output |
| --- | --- | --- |
| <ul> <li> <p>992424254-8</p> </li> <li> <p>ISBN 2-22605257-7</p> </li> </ul> | <ul> <li> <p>Pattern: Other</p> </li> <li> <p>Operator: Regex</p> </li> <li> <p>^[ISBN]{4}[ ]{0,1}[0-9]{1}[-]{1}[0-9]{3}[-]{1}[0-9]{5}[-]{1}[0-9]{0,1}$</p> </li> </ul> | <ul> <li> <p>FALSE</p> </li> <li> <p>TRUE</p> </li> </ul> |
| <ul> <li> <p>Terminator</p> </li> <li> <p>Terminator&nbsp;2</p> </li> </ul> | <ul> <li> <p>Pattern: [a-zA-Z]+ (a word , case insensitive)</p> </li> </ul> | <ul> <li> <p>TRUE</p> </li> <li> <p>FALSE</p> </li> </ul> |

## Match similar text

Checks if the cell is similar to a specific value. The function will return TRUE if the selected text is found within the cell, otherwise it will return FALSE.

A new column containing the result of the function will be created for each of the selected column. The default name of the new columns will be `<original_column_name>_matches`.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Use with | <ul> <li> <p><span>Value</span>: Select this option to check the column against a fixed value, set in the <span>Reference</span> field.</p> </li> <li> <p><span>Other column</span>: Select this option to check the column against values from another column, set in the <span>Column</span> drop-down list.</p> </li> </ul> |
| Fuziness | Set a number that corresponds to the Levenshtein distance you want to use for the check. The Levenshtein distance represents the number of edits you need to make in order to transform a string into another. |

### Example

| Input | Configuration | Output |
| --- | --- | --- |
| <ul> <li> <p>Remark</p> </li> <li> <p>Remodel</p> </li> </ul> | <ul> <li> <p>Use with: Value</p> </li> <li> <p>Reference: Remake</p> </li> <li> <p>Fuziness: 2</p> </li> </ul> | <ul> <li> <p>TRUE</p> </li> <li> <p>FALSE</p> </li> </ul> |

## Remove non alpha numeric characters

Removes all characters that are not 0-9, Aa-Zz, and accented letter from the content of a cell. The following character, as well as whitespaces, are kept: 1234567890abcdefghijklmnopqrstuvwxyzáàâäãåæçéèêëíìîïñóòôöõøœßúùûüABCDEFGHIJKLMNOPQRSTUVWXYZÁÀÂÄÃÅÆÇÉÈÊËÍÌÎÏÑÓÒÔÖÕØŒSSÚÙÛÜ.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

### Example

| Input | Output |
| --- | --- |
| Q=+L\*\*€IK#$ 12%3,4 | QLIK 1234 |

## Remove non numeric characters

Remove all characters that are not 0-9. Decimal separators and whitespaces are also kept.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

### Example

| Input | Output |
| --- | --- |
| Q=+L\*\*€IK#$12%3,4 | 123,4 |

## Remove part of text

Removes the specified strings from the cells in the column.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Operator | Select the operator that will be used to match the text to remove:<br><br><ul> <li> <p>Equals</p> </li> <li> <p>Contains</p> </li> <li> <p>Starts with</p> </li> <li> <p>Ends with</p> </li> </ul> |
| Value | Enter the character or string to remove. |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

### Example

| Input | Configuration | Output |
| --- | --- | --- |
| <ul> <li> <p>New York</p> </li> <li> <p>New york</p> </li> <li> <p>Yorkshire</p> </li> </ul> | <ul> <li> <p>Operator: Contains</p> </li> <li> <p>Value:York </p> </li> </ul> | <ul> <li> <p>New</p> </li> <li> <p>New york</p> </li> <li> <p>shire</p> </li> </ul> |

## Remove trailing and leading characters

Removes trailing and leading spaces or other specified characters.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Padding character | <ul> <li> <p>Whitespace: Unnecessary blank spaces at the beginning and end of the cells will be removed.</p> </li> </ul> |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

### Example

| Input | Output |
| --- | --- |
| [][]TableRecipe[] | TableRecipe |

## Search and replace

Replaces cells or parts of cells which contain a specific value with another one.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Operator | Select the operator that will be used to match the text to search:<br><br><ul> <li> <p>Equals</p> </li> <li> <p>Contains</p> </li> <li> <p>Starts with</p> </li> <li> <p>Ends with</p> </li> <li> <p>Matches regex</p> </li> <li> <p>Matches wildcard</p> </li> </ul> |
| Value | Enter the character or string to search. |
| Replace | Select the behavior of the replace action:<br><br><ul> <li> <p>Matching text with replace value</p> </li> <li> <p>Entire cell with replace value</p> </li> <li> <p>Entire cell with null</p> </li> </ul> |
| Replace with | Enter the value with which to replace the specified strings. |
| Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |

Tip noteAlternatively, you can start a search and replace operation in a column by right-clicking a cell in the table and selecting the Replace this value in this column option. A recipe step then opens where the string to replace is already set. Finish the step configuration and click Apply.

### Example

| Input | Configuration | Output |
| --- | --- | --- |
| <ul> <li> <p>France</p> </li> <li> <p>Canada</p> </li> <li> <p>United States of America</p> </li> <li> <p>United States</p> </li> </ul> | <ul> <li> <p>Operator: Contains</p> </li> <li> <p>Value: America</p> </li> <li> <p>Replace with: USA</p> </li> <li> <p>Overwrite entire cell</p> </li> </ul> | <ul> <li> <p>France</p> </li> <li> <p>Canada</p> </li> <li> <p>USA</p> </li> <li> <p>United States</p> </li> </ul> |

## Split column

Splits the content of a cell in several columns, based on a separator.

### Properties

| Property | Configuration |
| --- | --- |
| Columns to process | Select the column or columns on which you want to apply the function.<br><br>If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
| Parts | Enter the number of columns you want to create.<br><br>If the original string contains more parts than what you have specified in the Parts field, the remaining string will be returned in the last created column. |
| Separator | From the drop-down list, select the character used as basis for the split:<br><br><ul> <li> <p>,</p> </li> <li> <p>:</p> </li> <li> <p>;</p> </li> <li> <p>.</p> </li> <li> <p>-</p> </li> <li> <p>@</p> </li> <li> <p>Space</p> </li> <li> <p>Other (String). When using this option, enter your custom value in the <span>Custom separator</span> field.</p> </li> </ul> |

### Example

| Input | Configuration | Output |
| --- | --- | --- |
| Column A: 12/07/1998 | <ul> <li> <p>Parts: 3</p> </li> <li> <p>Separator: Other (String)</p> </li> <li> <p>Custom separator: /</p> </li> </ul> | <ul> <li>Column A: 12/07/1998</li> <li> <p>Column B: 12</p> </li> <li> <p>Column C: 07</p> </li> <li> <p>Column D: 1998</p> </li> </ul> |
| Column A: 20-03-2025 | <ul> <li> <p>Parts: 2</p> </li> <li> <p>Separator: -</p> </li> </ul> | <ul> <li>Column A: 20-03-2025</li> <li> <p>Column B: 20</p> </li> <li> <p>Column C: 03-2025</p> </li> </ul> |