SQL String Functions in SAP HANA with Examples and syntax

SQL String Functions in SAP HANA with Examples and syntax:-

The SQL string Function in SAP HANA contains something different then the SQL SERVER.Below all the SQL functions are explained one by one with examples and with syntax.

1.ASCII(‘character_value’) or ASCII(number): functions returns the ASCII value of the first character in the input

SELECT ASCII(‘Abcd’) AS “ASCII VALUE” FROM DUMMY
 OutPut:65 (ASCII Value of ‘A’)

2.BINTOSTR(‘encoded_value’): function converts VARBINARY string binary string into character string using CESU-8 encoding. This will be useful when we want to do any data encoding in Data Warehouse system

Example: SELECT BINTOSTR (‘41626364’) AS “BINTOSTR” FROM DUMMY;
Result: Abcd (Decoded value for ‘41626364’)
Example: SELECT BINTOSTR (‘393839’) AS “BINTOSTR” FROM DUMMY;
Result: 989 (Decoded value for 393839)

[AdSense-A]

3.CHAR(ASCII_VALUE): This is used to get character value for the ASCII value.

Example: SELECT CHAR(65) || CHAR(98) || CHAR(99) || CHAR(100) AS “CHARACTER” FROM DUMMY
Result: Abcd (Concatenation of ASCII values for ‘A’,’b’,’c’,’d’)

Example: SELECT CHAR(57) || CHAR(56) || CHAR(57) AS “NUMBER” FROM DUMMY
Result: 989 (Concatenation of ASCII values for 9,8,9)

4.CONCAT(string1,string2): This is used to concatenate more than one string values.

 Example: SELECT CONCAT(‘HELLO’,’ WORLD’) AS “FULL STRING” FROM DUMMY
Result: HELLO WORLD (combined string of ‘HELLO’ and ‘ WORLD’)

Example: SELECT CONCAT(‘HELLO’,CONCAT(‘ WORLD’,’ GOOD MORNING’)) FROM DUMMY
Result: HELLO WORLD GOOD MORNING (concatenation of 3 strings)

5.INSTR(string,search_string): This function returns the position of the specified string in the input string.

Example: SELECT INSTR(‘Abcd’,’c’) FROM DUMMY
Result: 3 (c position in the input string ‘Abcd’)

6.LCASE(‘string’): This converts the given input characters into lower case.

Example: SELECT LCASE(‘AbCD’) AS “LOWER CASE” FROM DUMMY
Result: abcd (Lower case characters for ‘AbCD’)

[AdSense-A]

7. LEFT(‘string’,n): This returns the number of characters/bytes specified from the left side in the input string.

 Example: SELECT LEFT(‘Abcd’,2) AS “LEFT STRING” FROM DUMMY
Result: Ab (Left two characters from ‘Abcd’)

8.LENGTH(string): This gives the length of the given string as the number. The starting number is always 1.

 
Example: SELECT LENGTH(‘Hello World’) AS “STRING LENGTH” FROM DUMMY
Result: 11 (length of string ‘Hellow World’)

9. LOCATE(string, substring): This gives the starting position of the specified substring in a given input string

 Example: SELECT LOCATE(‘Hello World’,’World’) AS “LOCATE” FROM DUMMY
Result: 7 (Starting position of World)

Example: SELECT LOCATE(‘Hello World’,’Dorld’) AS “LOCATE” FROM DUMMY
Result: 0 (word ‘Dorald’is not there in ‘Hello World’)

10.LOWER(string): This converts all the characters in the given string to lower case. This is same as LCASE function.

Example: SELECT LOWER(‘AbCD’) AS “LOWER CASE” FROM DUMMY
Result: abcd (lower case letters for string ‘AbCD’)

11.LPAD(string, length, pattern): This function is used to do padding on the left side for the given input string. If the pattern is not specified, then it places spaces or it uses the specified pattern for padding.

Example: SELECT LPAD(‘Abcd’,10,’123′) AS “LPAD” FROM DUMMY
 Result: 123123Abcd (123 is padded on the left side until the string length becomes 10)
 Example: SELECT LPAD(‘Abcd’,10) AS “LPAD” FROM DUMMY
 Result: ‘ Abcd’ (system pads 6 spaces before ‘Abcd’ to make total length 10)

[AdSense-A]

12.LTRIM(string,remove_pattern): Thus function removes all the leading spaces in the given input string. If the specific pattern is mentioned then it removes that pattern from the input string.

Example: SELECT LTRIM(‘ Abcd’) AS “LTRIM” FROM DUMMY
Result: Abcd (All the leading spaces are removed)
Example: SELECT LTRIM(‘Abcd’,’Ab’) AS “LTRIM” FROM DUMMY
Result: cd (‘Ab’ is removed from the string)

11. NCHAR(number): This function returns the UNICODE character for the given input number.

 Example: SELECT NCHAR(97) AS “NCHAR” FROM DUMMY
Result: a (character for the number 97)

12.REPLACE(string,search_string,replace_string): This function can be used to replace specific string pattern with different one in the input string.

Example: SELECT REPLACE (‘Abcd’,’cd’, ‘CD’) “REPLACE” FROM DUMMY;
Result: AbCD (cd has been replaced with CD)

13. RIGHT(string,number): This function returns the number of characters in input string from the right based on the specified number.

 Example: SELECT RIGHT(‘Abcd’,2) AS “RIGHT STRING” FROM DUMMY;
Result: cd (two characters from right in ‘Abcd’)

[AdSense-A]

14. RPAD(string, length, pattern): This function is used to do padding on the right side for the given input string. If the pattern is not specified, then it places spaces or it uses the specified pattern for padding.

 Example: SELECT RPAD(‘Abcd’,10,’123′) AS “RPAD” FROM DUMMY
Result: Abcd123123 (123 is padded on the right side until the string length becomes 10)
Example: SELECT RPAD(‘Abcd’,10) AS “RPAD” FROM DUMMY
Resut: ‘Abcd ’ (system pads 6 spaces after ‘Abcd’ to make total length 10)

15. RTRIM(string,remove_pattern):  Thus function removes all the trailing spaces in the given input string. If the specific pattern is mentioned then it removes that pattern from the input string.

Example: SELECT RTRIM(‘Abcd ‘) AS “RTRIM” FROM DUMMY
 Result: Abcd (All the trailing spaces are removed)
 Example: SELECT RTRIM(‘Abcd’,’cd’) AS “RTRIM” FROM DUMMY
 Result: Ab (‘cd’ is removed from the string)

16. STRTOBIN(string, codepage) : This function converts all the characters in the input string into a binary encoding with defined codepage.

Example: SELECT STRTOBIN (‘Abcd’, ‘UTF-16BE’) “STRTOBIN” FROM DUMMY
Result: 0041006200630064 (encoded value for ‘Abcd’)

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.