Core SAP HANA SQL script concepts- String Functions

Welcome to the fifth installment of this SAP HANA SQL Scripts core concepts section where we try to understand how to use different string functions and operators to manipulate character data sets.

The String manipulation begins…

It’s now time to pick up the EMP_NAMES table which contains the first name and last name of each employee. Let’s first do a simple SELECT to see the table data entirely.

The executed result looks as the below.

HANA SQL PIPE || Operator

Now, with this table, we can look at some more features of character string selection. What if you have these fields, first name and last name but you need a field that shows full name? Obviously, full name is the concatenation of these two fields. To concatenate two strings, we use a pipe operator || as shown below.

The result is shown as below. We’re almost there but sadly, we missed the blank space between first and last name.

But don’t worry, this is an easy fix. Concatenate first name, a blank space and the last name field using two pipe operators as shown below As you can clearly see, we get a blank space by enclosing a space between two single quotes.

Now as seen from the execution result, the full name looks as we expect. The column name is still the formula which we already know how to fix.

Alias comes to the rescue! We use the AS keyword in front of the formula and name it FULL_NAME.

As seen below, this looks good now.

There are many string functions that you can use in SAP HANA SQL Script. Use this link from SAP to know more.

Let me know if the link stops working in the future by using the comment section below.

SQL String functions – LEFT ()

The below example explains how to display left section of any character string in SELECT statement. The LEFT function helps us achieve this. Just use LEFT(<field_name>,<nr_of_characters>). The below use of function asks HANA to pick up the first 2 characters from first name field.

The below execution confirms the result.

Now, let’s consider a use-case where you need a field INITIAL which should display the first character from first name and last names fields concatenated together. To do this, we use both the concepts we just learnt. As seen below, we use LEFT to cut the first name only to the first letter and then concatenate the last name with a similar LEFT function using a pipe operator.

The execution looks as expected.

 

I hope this was easy to understand and follow. In the next tutorial, we learn how to represent and report on data that is “nothing” and understand how we can negate an entire selection filter using the NOT keyword.

Make sure to share this content on social media using the share buttons below to show your support to this website and to keep it alive.

<<Previous Tutorial                                                                                                              Next Tutorial>>