String Functions

You can perform various character manipulations using string functions.

String functions include:

• CHAR'(' value_returned_expression ')': Converts a numeric value between 0 and 255 to the character value corresponding to the ASCII code. If the input numeric value is greater than 255, the function uses number % 256 to wrap.

  Inputs: An integral numeric expression.

  Example: CHAR(35)

• CONCAT_WS'(' sep , value_returned_expression_list ')': Joins multiple strings using a specified separator.

  Inputs: A string.

  Example: CONCAT_WS(' ',CUSTOMERS.CUST_FIRST_NAME,CUSTOMERS.CUST_LAST_NAME)

• LOWER'(' value_returned_expression ')': Converts all characters of the string to lowercase.

  Inputs: A string expression.

  Example: LOWER(CUSTOMERS[CUST_FIRST_NAME])

• SUBSTR'(' value_returned_expression, pos [, len] ')': Extracts a substring starting at position and length.

  Inputs: A string, starting position (pos) and length (len).

  Example: SUBSTR(CUSTOMERS[CUST_FIRST_NAME],1,4)

• REGEXP_EXTRACT'(' value_returned_expression, regexp , idx ')': Returns the substring in the input that matches the regular expression group at idx.

  Inputs: A string, a regular expression and a group index integer value.

  Example: REGEXP_EXTRACT("100-200", "(d+)",1)

• CONCAT'(' value_returned_expression_list ')': Combines multiple strings into a single string.

  Inputs: String expressions.

  Example: CONCAT(CUSTOMERS.CUST_FIRST_NAME,CUSTOMERS.CUST_LAST_NAME)

• TRIM'(' value_returned_expression ')': Removes leading and trailing spaces from a string.

  Inputs: A string expression.

  Example: TRIM(CUSTOMERS.CUST_FIRST_NAME)

• UPPER'(' value_returned_expression ')': Converts all characters of the string to uppercase.

  Inputs: A string expression.

  Example: UPPER(CUSTOMERS[CUST_FIRST_NAME])

• INSTR'(' str, substr ')': Returns the index of the first occurrence of substr in str (1-based).

  Inputs: A string and the substring to search for.

  Example: INSTR(CUSTOMERS[CUST_FIRST_NAME],"Alex")

• ASCII'(' value_returned_expression ')': Returns the ASCII numeric value of the first character in the string.

  Inputs: A string expression.

  Example: ASCII("Alex")

• LEFT'(' str, len ')': Returns the leftmost length (len) characters from the string (str). Returns an empty string if len <= 0.

  Inputs: A string (str) and a numeric length.

  Example: LEFT(CUSTOMERS[CUST_FIRST_NAME],2)

• REPLACE'(' str, search[, replace] ')': Replaces all occurrences of search from the string (str) with replace (the default is an empty string).

  Inputs: A string, search term, and optional replacement string.

  Example: REPLACE(CUSTOMERS[CUST_VALID],"I","*")

• RIGHT'(' str, len ')': Returns the rightmost length (len) characters from the string (str). Returns an empty string if len <= 0.

  Inputs: A string (str) and a numeric length.

  Example: RIGHT(CUSTOMERS[CUST_FIRST_NAME],2)

• SPACE'(' value_returned_expression ')': Returns a string of spaces with length equal to the numeric value.

  Inputs: An integer expression.

  Example: SPACE(3)

• LENGTH(' value_returned_expression ')': Returns a number representing the total number of characters in the input string, including spaces.

  Inputs: An integer expression.

  Example: LENGTH(CUSTOMERS[CUST_FIRST_NAME])

• HASH(' value_returned_expression_list ')': Returns a number for hash value of the arguments.

  Inputs: Any expression or expressions list.

  Example: HASH(CUSTOMERS.CUST_FIRST_NAME,CUSTOMERS.CUST_LAST_NAME)