Table of Contents
Additional Custom Functions
This page details the additional custom functions available in DQ for Excel.
DQ.CASE_STRING
Cases a string.
Parameters:
input (string), text to case
caseType (string), type of case, from list:
- ProperCase_FamilyName
- ProperCase_Address
- UpperCase
- LowerCase
- TitleCase
language (string), language of input, from list:
- English
- Spanish
- French
- Italian
- German
returns (string), cased string
Examples:
input | caseType | language | output |
---|---|---|---|
someTEXT | uppercase | english | SOMETEXT |
someTEXT | lowercase | english | sometext |
DQ.IS_ALL_UPPER
Checks if string is made up of only upper case characters.
Parameters:
input (string), text to check
returns (boolean), true if all upper case
input | output |
---|---|
SOMETEXT | TRUE |
someTEXT | FALSE |
DQ.IS_ALL_LOWER
Checks if a string is made up of only lower case characters
Parameters:
input (string), text to check
returns (boolean), true if all lower case
input | output |
---|---|
sometext | TRUE |
someTEXT | FALSE |
DQ.IS_MIXED_CASE
Checks if a string is made up of mixed case characters.
Parameters:
input (string), text to check
returns (boolean), true if mixed case
input | output |
---|---|
someTEXT | TRUE |
sometext | FALSE |
DQ.IS_ALPHA_NUMERIC
Checks if a string is made up of alpha numeric characters.
Parameters:
input (string), text to check
returns (boolean), true if alpha numeric
input | output |
---|---|
s0meT3XT | TRUE |
s!m?T3XT | FALSE |
DQ.IS_NUMERIC
Checks if a string is made up of numeric characters.
Parameters:
input (string), text to check
returns (boolean), true if numeric
input | output |
---|---|
12345678 | TRUE |
someTEXT | FALSE |
DQ.IS_ISO4217_CURRENCY_CODE
Checks if a string is a ISO 4217 currency code. See ISO-4127-currency-codes for more information.
Parameters:
input (string), text to check
returns (boolean), true if ISO 4217 currency code
input | output |
---|---|
EUR | TRUE |
EURO | FALSE |
DQ.IS_ISO2_CODE
Checks if a string is an ISO 3166-1 alpha-2 country code. See ISO 3166 country codes for more information.
Parameters:
input (string), text to check
returns (boolean), true if ISO2 code
input | output |
---|---|
GB | TRUE |
GBR | FALSE |
DQ.IS_ISO3_CODE
Checks if a string is an ISO 3166-1 alpha-3 country code. See ISO 3166 country codes for more information.
Parameters:
input (string), text to check
returns (boolean), true if ISO3 code
input | output |
---|---|
GBR | TRUE |
GB | FALSE |
DQ.REMOVE_LEADING
Removes a value from the start of a string.
Parameters:
input (string), text to modify
valueToRemove (string), value to remove from text
leaveOneAtStart (boolean), if true leave one occurrence at the start of the string
returns (string) modified text
input | valueToRemove | leaveOneAtStart | output |
---|---|---|---|
mymymymymyTestString | my | TRUE | myTestString |
mymymymymyTestString | my | FALSE | TestString |
DQ.REMOVE_CHARACTERS
Removes a type of character from a string.
Parameters:
input (string), text to modify
characterType (string), type of character, from list:
- Digit
- Letter
- LetterOrDigit
- Punctuation
- Whitespace
- Upper
- Lower
- Symbol
- UpperCaseVowel
- LowerCaseVowel
- UpperCaseConsonant
- LowerCaseConsonant
- OddDigit
- EvenDigit
- NonPrinting
leaveOneAtStart (boolean), if true leave one occurrence at the start of the string
returns (string), modified text
input | characterType | leaveOneAtStart | output |
---|---|---|---|
Te123st | Digit | FALSE | Test |
Te123st | Letter | FALSE | 123 |
Te12££st | LetterOrDigit | FALSE | ££ |
Te.st | Punctuation | FALSE | Test |
Te st | Whitespace | FALSE | Test |
teEst | Upper | FALSE | Test |
TEeST | Lower | FALSE | Test |
Te$$st | Symbol | FALSE | Test |
TeEst | UpperCaseVowel | FALSE | Test |
TeEst | LowerCaseVowel | FALSE | TEst |
teTst | UpperCaseConsonant | FALSE | test |
TetST | LowerCaseConsonant | FALSE | TeST |
Te1st | OddDigit | FALSE | Test |
Te2st | EvenDigit | FALSE | Test |
Test | NonPrinting | FALSE | Test |
DQ.REMOVE_SINGLE_CHARACTER_WORDS
Removes single character words from a string.
Parameters:
input (string), text to modify
returns (string), modified text
input | output |
---|---|
this is a very long string I think | this is very long string think |
DQ.REPLACE_ADJACENT_REPEATING_TEXT
Replaces adjacent repeating text.
Parameters:
input (string), text to modify
repeatingValue (string), value to find and replace
replacement (string), replace value
returns (string), modified text
input | repeatingValue | replacement | output |
---|---|---|---|
exaaaaaaaample aababaaa | a | @ | exa@@@@@@@mple a@baba@@ |
DQ.REPLACE_IF_ENDS_WITH
Replaces a piece of text at the end of a string if found.
Parameters:
input (string), text to modify
repeatingValue (string), substring to replace, at the end of string
replacement (string), replace value
returns (string), modified text
input | repeatingValue | replacement | output |
---|---|---|---|
this is an input | put | ?? | this is an in?? |
DQ.REPLACE_IF_STARTS_WITH
Replaces a piece of text at the start of a string if found.
Parameters:
input (string), text to modify
repeatingValue (string), substring to replace, at the start of string
replacement (string), replace value
returns (string), modified text
input | repeatingValue | replacement | output |
---|---|---|---|
this is an input | th | ?? | ??is is an input |
DQ.STRING_TO_BINARY
Converts a string to binary.
Parameters:
input (string), text to modify
returns (string), modified text
input | output |
---|---|
test | 01110100011001010111001101110100 |
DQ.BINARY_TO_STRING
Converts a binary code into a string
Parameters:
input (string), text to modify
returns (string), modified text
input | output |
---|---|
01110100011001010111001101110100 | test |
DQ.STRING_TO_HEX
Converts a string to hexadecimal.
Parameters:
input (string), text to modify
returns (string), modified text
input | output |
---|---|
test | 74657374 |
DQ.HEX_TO_STRING
Converts a hexadecimal string to binary.
Parameters:
input (string), text to modify
returns (string), modified text
input | output |
---|---|
74657374 | test |
DQ.REVERSE
Reverse a given string.
Parameters:
input (string), text to reverse
returns (string), reversed text
input | output |
---|---|
myString | gnirtSym |
DQ.NORMALIZE_WHITE_SPACE
Normalizes white space in a string by collapsing white space into single space characters.
Parameters:
input (string), text to modify
returns (string), modified text
input | output |
---|---|
my—string—-with—-weird-white–space | my string with weird white space |
Note The values in the input column use dashes (-) to represent spaces.
DQ.NORMALIZE_ALPHA_NUMERIC_PHONE
Normalizes alpha numeric characters in a given phone number.
Parameters:
input (string), text to modify
returns (string), modified text
input | output |
---|---|
1-800-flowers | 1-800-3569377 |
DQ.COLLAPSE_ADJACENT_REPEATED_CHARACTERS
Collapses adjacent repeated characters.
Parameters:
input (string), text to modify
collapseNumerics (boolean), true if numbers should be collapsed
maximumRepeat (number), the number of characters allowed before collapsing
returns (string), modified text
input | collapseNumerics | maximumRepeat | output |
---|---|---|---|
Silly | Sily | ||
Silly4455667777788890 | TRUE | Sily4567890 | |
Silly4455667777788890 | FALSE | Sily4455667777788890 | |
Sillllllly | FALSE | 1 | Sillllllly |
Sillllly5533669999 | TRUE | 2 | Sillllly5533669999 |
DQ.COLLAPSE_ADJACENT_REPEATED_TYPE
Collapses adjacent repeated types of character.
Parameters:
input (string), text to modify
maximumRepeat (number), the number of characters allowed before collapsing
type (string), type of character to check for, from list:
- Letter
- LowerCaseLetter
- UpperCaseLetter
- UpperCaseVowel
- LowerCaseVowel
- UpperCaseConsonant
- LowerCaseConsonant
- LetterOrNumber
- Number
- Punctuation
- Whitespace
- Symbol
- NonPrinting
returns (string), modified text
input | collapseNumerics | maximumRepeat | output |
---|---|---|---|
TeeestTT | 1 | Letter | TestT |
TeeeestTT | 1 | LowerCaseLetter | TestTT |
TeeeestTT | 1 | UpperCaseLetter | TeeeestT |
TeeEEEstTT | 1 | UpperCaseVowel | TeeEstTT |
TeeEEEstTT | 1 | LowerCaseVowel | TeEEEstTT |
TeeEEEstTT | 1 | UpperCaseConsonant | TeeEEEstT |
TeeEEEssstTT | 1 | LowerCaseConsonant | TeeEEEstTT |
TeeEEst11223333 | 1 | LetterOrNumber | TeEst123 |
TeeEEst11223 | 1 | Number | TeeEEst123 |
TeeEEstTT??..??? | 1 | Punctuation | TeeEEstTT?.? |
Te EE stTT | 1 | Whitespace | Te EE stTT |
Te£££st$$TT | 1 | Symbol | Te£st$TT |
TeeEest | 1 | NonPrinting | TeeEest |
DQ.FILTER_STOP_WORDS
Filters out stop words from a string.
Parameters:
input (string), text to modify
returns (string), modified text
input | output |
---|---|
my noisy string with a lot of stop words | noisy string lot stop words |
DQ.RETAIN_CHARACTERS
Retains given characters in a string, the remaining characters are replaced with the given replacement string.
Parameters:
input (string), text to modify
replacement (string), character to replace with
charactersToRetain (string), characters to not replace
returns (string), modified text
input | replacement | charactersToRetain | output |
---|---|---|---|
this is an input | ? | i | ??i??i?????i???? |
DQ.EXTRACT_CHARACTERS
Extracts a given number of characters from the start or end of a string.
Parameters:
input (string), text to modify
extractLength (string), number of characters to extract
extractFrom (string), the point of the string to extract from, from list:
- Start
- End
returns (string), modified text
input | extractLength | extractFrom | output |
---|---|---|---|
myTestString | 3 | Start | myT |
myTestString | 3 | End | ing |
DQ.EXTRACT_WORDS
Extracts number of words from a string.
Parameters:
input (string), text to modify
extractLength (string), number of words to extract
extractFrom (string), the point of the string to extract from, from list:
- Start
- End
returns (string), modified text
input | extractLength | extractFrom | output |
---|---|---|---|
my test string | 1 | Start | my |
my test string | 2 | End | test string |
DQ.REMOVE_HTML
Removes HTML tags from a string.
Parameters:
input (string), text to modify
returns (string), modified text
input | output |
---|---|
Test <p id=myAttr>someText</p> | Test someText |
DQ.STARTS_WITH
Check if a string starts with a given value.
Parameters:
input (string), text to check
checkFor (string), value to check for
returns (boolean), true if starts with value
input | checkFor | output |
---|---|---|
myString | my | true |
myString | mi | false |
DQ.ENDS_WITH
Parameters:
input (string), text to check
checkFor (string), value to check for
returns (boolean), true if starts with value
input | checkFor | output |
---|---|---|
myString123 | 123 | true |
myString123 | 456 | false |
DQ.ENSURE_ENDS_WITH
Ensure a string ends with a given value.
Parameters:
input (string), text to modify
suffix (string), value at the end of the string
returns (string), modified text
input | suffix | output |
---|---|---|
test_endString | endString | test_endString |
test_ | endString | test_endString |
DQ.ENSURE_STARTS_AND_ENDS_WITH
Ensures a string starts and ends with a given value.
Parameters:
input (string), text to modify
topAndTail (string), value to check for
returns (string), modified text
input | suffix | output |
---|---|---|
testString_test_testString | testString | testString_test_testString |
_test_ | testString | testString_test_testString |
DQ.ENSURE_STARTS_WITH
Ensures a string starts with a given value.
Parameters:
input (string), text to modify
suffix (string), value at the start of the string
returns (string), modified text
input | suffix | output |
---|---|---|
startString_test | startString | startString_test |
_test | startString | startString_test |
DQ.STARTS_WITH_TYPE
Checks if a string starts with a given type of character.
Parameters:
input (string), text to check
type (string), type of value to check for, from list:
- Letter
- LowerCaseLetter
- UpperCaseLetter
- UpperCaseVowel
- LowerCaseVowel
- UpperCaseConsonant
- LowerCaseConsonant
- LetterOrNumber
- Number
- Punctuation
- Whitespace
- Symbol
- NonPrinting
returns (boolean), true if found
input | type | output |
---|---|---|
test | LowerCaseLetter | true |
Test | UpperCaseLetter | true |
Etest | UpperCaseVowel | true |
eTest | LowerCaseVowel | true |
Test | UpperCaseConsonant | true |
test | LowerCaseConsonant | true |
1test | LetterOrNumber | true |
2test | Number | true |
.test | Punctuation | true |
test | Whitespace | true |
£test | Symbol | true |
test | NonPrinting | true |
DQ.ENDS_WITH_TYPE
Checks if a string ends with a given type of character.
Parameters:
input (string), text to check
type (string), type of value to check for, from list:
- Letter
- LowerCaseLetter
- UpperCaseLetter
- UpperCaseVowel
- LowerCaseVowel
- UpperCaseConsonant
- LowerCaseConsonant
- LetterOrNumber
- Number
- Punctuation
- Whitespace
- Symbol
- NonPrinting
returns (boolean), true if found
input | type | output |
---|---|---|
testT | Letter | true |
testt | LowerCaseLetter | true |
testT | UpperCaseLetter | true |
testA | UpperCaseVowel | true |
testa | LowerCaseVowel | true |
testT | UpperCaseConsonant | true |
testt | LowerCaseConsonant | true |
testT | LetterOrNumber | true |
test1 | Number | true |
test. | Punctuation | true |
test | Whitespace | true |
test$ | Symbol | true |
test | NonPrinting | true |