SAP HANA String Functions: Syntax and Examples

Formulas & functions · Updated November 22, 2026

SAP HANA string functions manipulate text in SQL: they join strings, extract parts, find characters, change case, remove spaces, and replace text. Common choices include CONCAT or ||, SUBSTRING, LOCATE, UPPER, LOWER, TRIM, and REPLACE.

What string functions do in HANA

A string function takes one or more values and returns a text value, a position, or a count. You can use one in a SELECT list to shape output, in a WHERE condition to filter rows, or as part of an expression that feeds another function. The same operation may be written in different ways: for example, HANA supports both the CONCAT function and the || concatenation operator.

HANA SQL works with character data types such as VARCHAR and NVARCHAR, as well as large character types. The input type and the function’s supported argument types can matter, particularly when expressions mix text with numbers or other data types. Cast values explicitly when implicit conversion would make a query unclear or unreliable.

These are SQL functions, not spreadsheet functions. You run them in a HANA SQL statement, such as a query in a database tool or SQL console; their syntax is not Excel formula syntax.

Join strings with CONCAT or ||

CONCAT joins two string arguments. For multiple pieces, nest calls or use the || operator, which is often easier to read in a longer expression.

SELECT CONCAT(first_name, last_name) AS full_name
FROM customers;

This puts the values directly together, so a space must be added if the output should have one. The operator makes that visible:

SELECT first_name || ' ' || last_name AS full_name
FROM customers;

Be deliberate about NULL. A missing name is not necessarily the same as an empty string, and concatenation involving a NULL can produce a NULL result. If the report should treat a missing value as blank, handle it explicitly with COALESCE:

SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name
FROM customers;

This preserves a space even if one name is missing. If that is undesirable, build the output according to the desired missing-value rules rather than assuming that every null should become blank.

Extract text and measure its length

SUBSTRING returns part of a string. Its HANA form is SUBSTRING(string, start, length); the start position is one-based, so position 1 is the first character. For example, to select the first three characters of a product code:

SELECT SUBSTRING(product_code, 1, 3) AS code_prefix
FROM products;

The start and length can also come from columns or expressions. Check the data before relying on a fixed length: short or irregular values may not have the expected structure. If the goal is to get the text on one side of a delimiter, finding the delimiter’s position and then extracting the relevant part can be clearer than assuming a fixed format.

LENGTH returns the length of a string in characters. It is useful for checking whether a value is blank or for identifying values outside an expected size:

SELECT product_code, LENGTH(product_code) AS code_length
FROM products;

Spaces are characters too, so a value containing only spaces is not necessarily equivalent to an empty string. Use trimming when the business rule considers leading or trailing spaces irrelevant. Also distinguish character length from storage size: a character count does not tell you how many bytes a value occupies in its encoding.

Find text with LOCATE

LOCATE searches a string for another string and returns the position where it is found. Its argument order is the text to search first, then the text to find. For example, this reports where the word HANA begins in a product description:

SELECT LOCATE(description, 'HANA') AS hana_position
FROM products;

The returned position is one-based. If the search text is not present, LOCATE returns 0, which makes it possible to test for a match in a condition:

SELECT product_id, description
FROM products
WHERE LOCATE(description, 'HANA') > 0;

HANA’s syntax also allows optional starting-position and occurrence arguments, useful when searching for a later occurrence. When combining position results with SUBSTRING, remember that a missing search term produces 0; using that value as an extraction position can lead to an invalid or unintended result. Test for the delimiter before extracting around it.

Change case and remove unwanted characters

UPPER converts text to uppercase and LOWER converts it to lowercase. These are handy for display normalization or for comparing text without regard to ordinary letter case:

SELECT UPPER(country_code) AS normalized_code
FROM customers;

Applying LOWER to both sides of a comparison can be one way to perform a case-normalized match, but it may affect how a query uses indexes or other optimizations. If case-insensitive matching is a frequent requirement, consider the database’s collation and data-modeling options, and check the behavior for the HANA version and column in use. Case conversion is not the same as removing accents or normalizing every Unicode character.

TRIM removes specified characters from the beginning, end, or both ends of a string. For the usual task of removing surrounding spaces, use:

SELECT TRIM(customer_name) AS clean_name
FROM customers;

The more explicit form can select a side and a trim character, such as TRIM(LEADING '0' FROM account_code) to remove leading zero characters. The trim character is treated as a character to remove, not as a multi-character word that must match as a whole. For one-sided cleanup, HANA also provides LTRIM and RTRIM. Verify the exact cleanup rule before applying it to identifiers, where leading zeros may be meaningful.

Replace text and pad values

REPLACE substitutes occurrences of one string with another. Its basic form is REPLACE(string, search_string, replace_string). For example, to change hyphens to spaces in a displayed label:

SELECT REPLACE(category_name, '-', ' ') AS display_category
FROM categories;

Replacement is literal text substitution, not a general pattern-matching rule. For patterns, HANA has regular-expression functions, including functions in the REPLACE_REGEXPR and SUBSTR_REGEXPR families. Regular expressions are more expressive, but their syntax and escaping rules take care to get right; consult the SQL reference for the installed HANA release before using a specific expression.

LPAD and RPAD add padding to the left or right to reach a requested length. For instance, padding a short display code with leading zeroes can make values line up in a report. Do this only when the code is text and the zeros are for presentation: converting an identifier to a number first can discard meaningful leading zeros. Padding a value to a fixed width does not validate that the original value has the expected format.

Use string functions in filters and updates

String expressions are often useful in a query’s output, but putting a function around a column in a filter can have performance implications. For example, filtering on UPPER(country_code) may require HANA to evaluate the expression for candidate rows. For large tables, compare the execution plan and consider storing normalized values or using a suitable database design when that fits the application.

For simple text patterns, SQL’s LIKE operator is another option. The percent sign matches any sequence of characters, while the underscore matches one character. For example, description LIKE 'SAP%' matches descriptions beginning with SAP. Wildcards have special meaning, so a search for a literal percent or underscore needs an appropriate escape strategy. Use bound parameters for values supplied by an application; do not create SQL by concatenating untrusted input into a statement.

String functions can also appear in data-changing statements, but cleanup should be tested before applying it broadly. Preview the transformed values with a SELECT, especially when trimming, replacing, or changing case could alter keys or identifiers. For an UPDATE, restrict the affected rows with a deliberate condition and follow the database’s transaction and change-control practices.

Choosing the right function

Start with the result you need, then choose the smallest operation that expresses it. A quick guide:

  • Join two strings: use CONCAT; for several pieces, use nested calls or ||.
  • Take characters by position: use SUBSTRING.
  • Find where a piece of text occurs: use LOCATE.
  • Normalize letter case: use UPPER or LOWER.
  • Remove surrounding or one-sided characters: use TRIM, LTRIM, or RTRIM.
  • Substitute literal text: use REPLACE; use a regular-expression function only when pattern matching is truly needed.
  • Check size: use LENGTH, remembering that characters and bytes are different measures.

Function availability and details can vary by HANA release, and some functions have restrictions based on input type. When working with a managed HANA service or an older installation, use the SQL reference for that specific release rather than assuming every feature in a newer reference is available.

Frequently Asked Questions

How do I concatenate strings in SAP HANA SQL?
Use CONCAT(value1, value2) for two values or the || operator to join several expressions. Add a literal such as ' ' when you need a separator, and decide explicitly how null values should be handled.

Is SUBSTRING position zero-based in HANA?
No. The start position in SUBSTRING is one-based: 1 refers to the first character. Check positions calculated by other expressions before using them as substring starts.

What does LOCATE return when the text is not found?
LOCATE returns 0 when the search string is absent. Check for a position greater than 0 before using the result to extract text around a match.

How can I remove spaces from a HANA string?
Use TRIM(value) to remove surrounding spaces. Use LTRIM or RTRIM for one-sided trimming, and specify a trim character when the requirement is to remove something other than spaces.

Are HANA string functions case-sensitive?
Case handling depends on the operation and comparison context. UPPER and LOWER change letter case, but they do not remove accents or guarantee identical treatment of every Unicode character. Check the column’s comparison and collation behavior for the particular database.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.