SQL Query Techniques: Isolating Substrings in SAP HANA
In SAP HANA SQL, SUBSTR_BEFORE and SUBSTR_AFTER are the two functions that help in pulling out parts of words or phrases. They are essential for data manipulation and analysis.
SUBSTR_BEFORE grabs everything before a certain word or symbol, while SUBSTR_AFTER gets everything after it.
Syntax for the functions:
SUBSTR_BEFORE function
SUBSTR_BEFORE (string_expression, delimiter)
- string_expression: Refers to the column name or a string value from which you want to extract a substring.
- delimiter: The character or substring used as the delimiter to identify where to split the string. The function returns the part of `string_expression` before the first occurrence of `delimiter`.
SUBSTR_AFTER function
SUBSTR_AFTER (string_expression, delimiter)
- string_expression: Refers to the column name or a string value from which you want to extract a substring.
- delimiter: The character or substring used as the delimiter to identify where to split the string. The function returns the part of `string_expression` after the first occurrence of `delimiter`.
These functions are particularly valuable in scenarios where strings contain structured data, such as codes or identifiers separated by consistent patterns. For instance, if you have a bunch of order numbers that all contain ‘MR’, the SUBSTR_BEFORE function can quickly extract the numbers before ‘MR’. And if there’s any additional information after ‘MR’, then SUBSTR_AFTER can grab that part.
Example
In the provided table, we extract substrings from the column ‘U_AVA_GFREFNO’, isolating portions both before and after the delimiter ‘MR’.
SQL Queries for extracting substrings for the above:
SELECT A.”U_AVA_GFREFNO”, SUBSTR_BEFORE (A.”U_AVA_GFREFNO”, ‘MR’) as “OPDN_MRNNO_Before”, SUBSTR_AFTER (A.”U_AVA_GFREFNO”, ‘MR’) as “OPDN_MRNNO_After” FROM “PDN1”;
Closing Remarks on Text Data Manipulation in SAP HANA
These functions are useful for extracting segments of strings using a defined delimiter, enabling detailed manipulation of text data within SQL queries. Whether you’re breaking down codes or picking out key details, SUBSTR_BEFORE and SUBSTR_AFTER make SAP HANA SQL simpler and more powerful for getting the info you need from your text.
