ASCII- This functions returns the ASCII value of first character in the input.
Example: SELECT ASCII(‘Abcd’) AS “ASCII VALUE” FROM DUMMY
Result: 65 (ASCII Value of ‘A’)
Example: SELECT ASCII(989) AS “ASCII VALUE” FROM DUMMY
Result: 57 (ASCII Value of 9)
BINTOSTR- This 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.
Syntax: BINTOSTR(‘encoded_value’)
Example: SELECT BINTOSTR (‘41626364’) AS “BINTOSTR” FROM DUMMY;
Result: Abcd (Decoded value for ‘41626364’)
CHAR - CHAR: This is used to get character value for the ASCII value.
Syntax: CHAR(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’)
CONCAT - This is used to concatenate more than one string values.
Syntax: CONCAT(string1,string2)
Example: SELECT CONCAT(‘HELLO’,’ WORLD’) AS “FULL STRING” FROM DUMMY
Result: HELLO WORLD (combined string of ‘HELLO’ and ‘ WORLD’)
Note: CONCAT can only take two strings as input. If we have to combine more than two strings then CONCAT has to be more than once like below.
Example: SELECT CONCAT(‘HELLO’,CONCAT(‘ WORLD’,’ GOOD MORNING’)) FROM DUMMY
Result: HELLO WORLD GOOD MORNING (concatenation of 3 strings)
LCASE - This convers the given input characters into lower case.
Syntax: LCASE(‘string’)
Example: SELECT LCASE(‘AbCD’) AS “LOWER CASE” FROM DUMMY
Result: abcd (Lower case characters for ‘AbCD’)
LEFT- This returns the number of characters/bytes specified from the left side in the input string.
Syntax: LEFT(‘string’,n)
Example: SELECT LEFT(‘Abcd’,2) AS “LEFT STRING” FROM DUMMY
Result: Ab (Left two characters from ‘Abcd’)
LENGTH - This gives the length of the given string as number. The starting number is always 1.
Syntax: LENGTH(string)
Example: SELECT LENGTH(‘Hello World’) AS “STRING LENGTH” FROM DUMMY
Result: 11 (length of string ‘Hellow World’)
LOCATE - LOCATE: This give the starting position of the specified substring in a given input string
Syntax: LOCATE(string,substring)
Example: SELECT LOCATE(‘Hello World’,’World’) AS “LOCATE” FROM DUMMY
Result: 7 (Starting position of World)
Note: If the substring is not found in the main string then it gives the output as 0.
Example: SELECT LOCATE(‘Hello World’,’Dorld’) AS “LOCATE” FROM DUMMY
Result: 0 (word ‘Dorald’is not there in ‘Hello World’)
LOWER - This converts all the characters in the given string to lower case. This is same as LCASE function.
Syntax: LOWER(string)
Example: SELECT LOWER(‘AbCD’) AS “LOWER CASE” FROM DUMMY
Result: abcd (lower case letters for string ‘AbCD’)
LPAD - This function is useful to do padding on 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.
Syntax: LPAD(string,length,pattern)
Example: SELECT LPAD(‘Abcd’,10,’123′) AS “LPAD” FROM DUMMY
Result: 123123Abcd (123 is padded on the left side until the string lengthbecomes 10)
Example: SELECT LPAD(‘Abcd’,10) AS “LPAD” FROM DUMMY
Result: ‘ Abcd’ (system pads 6 spaces before ‘Abcd’ to make total length 10)
LTRIM- Thus function removes all the leading spaces in the given input string. If specific pattern is mentioned then it removes that pattern from the input string.
Note: The pattern is used as set of characters, not as search string.
Syntax: LTRIM(string,remove_pattern)
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)
NCHAR - This function returns the UNICODE character for the given input number.
Syntax: NCHAR(number)
Example: SELECT NCHAR(97) AS “NCHAR” FROM DUMMY
Result: a (character for the number 97)
REPLACE - This function can be used to replace specific string pattern with different one in the input string.
Syntax: REPLACE(string,search_string,replace_string)
Example: SELECT REPLACE (‘Abcd’,’cd’, ‘CD’) “REPLACE” FROM DUMMY;
Result: AbCD (cd has been replaced with CD)
Note: Some of the points to remember are
1. If the main string is NULL then the result is NULL
2. If the search string is not found in the main string, then same main string will be displayed as result.
RIGHT -This function returns the number of characters in input string from right based on the specified number.
Syntax: RIGHT(string,number)
Example: SELECT RIGHT(‘Abcd’,2) AS “RIGHT STRING” FROM DUMMY;
Result: cd (two characters from right in ‘Abcd’)
RPAD-This function is useful to do padding on 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.
Syntax: RPAD(string,length,pattern)
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)
RTRIM - Thus function removes all the trailing spaces in the given input string. If specific pattern is mentioned then it removes that pattern from the input string.
Note: The pattern is used as set of characters, not as search string.
Syntax: RTRIM(string,remove_pattern
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)
STRTOBIN - This function convers all the characters in the input string into a binary encoding with defined codepage.
Syntax: STRTOBIN(string,codepage)
Example: SELECT STRTOBIN (‘Abcd’, ‘UTF-16BE’) “STRTOBIN” FROM DUMMY
Result: 0041006200630064 (encoded value for ‘Abcd’)
SUBSTR_AFTER- This function gives the substring from the input string after the first occurrence of the specified pattern.
Note: Some of the notes to remember are
1. If the input string doesn’t have the specified pattern, then the result is NULL
2. If the specified pattern is empty, then entire string will be returned.
3. If the input string or patter is NULL, then the result is NULL.
Syntax: SUBSTR_AFTER(string,pattern)
Example: SELECT SUBSTR_AFTER(‘Hello World’,’ ‘) AS “SUSTR AFTER” FROM DUMMY
Result: World (substring after ‘ ‘ in Hello World is World)
SUBSTR_BEFORE -This function gives the substring from the input string before the first occurrence of the specified pattern.
Note: Some of the notes to remember are
1. If the input string doesn’t have the specified pattern, then the result is NULL
2. If the specified pattern is empty, then entire string will be returned.
3. If the input string or patter is NULL, then the result is NULL.
Syntax: SUBSTR_BEFORE(string,pattern)
Example: SELECT SUBSTR_BEFORE(‘Hello World’,’ ‘) AS “SUSTR BEFORE” FROM DUMMY
Result: Hello (substring before ‘ ‘ in Hello World is Hello)
SUBSTRING - This function returns the substring from the input string based on the stating position and number of characters specified.
Note: Some of the notes to remember are
1. If the start position is less than 0 then it is considered as 1.
2. If the input string length is less than 1 then empty sting will be returned.
3. If the length is not specified then entire string will be returned after the starting position.
Syntax: SUBSTRING(string,start_position,length)
Example: SELECT SUBSTRING(‘Hello World’,4,2) AS “SUBSTRING” FROM DUMMY
Result: lo (Starting from position 4 and 2 characters in ‘Hello World’)
Example: SELECT SUBSTRING(‘Hello World’,4) AS “SUBSTRING” FROM DUMMY
Result: lo World (starting from position 4, entire string is returned)
TRIM - This function can be used to remove both LEADING, TRAILING spaces from input string or specified string pattern.
Note: Some of the notes to remember are
1. If either input string, string pattern are NULL then NULL is returned.
2. If the option is not specified like LEADING or TRAILING then it moves both TRAILING and LEADING.
3. If the string pattern is not specified, then system consider it as single space.
Syntax: TRIM(LEADING/TRAILING/BOTH string_pattern FROM string)
Example: SELECT TRIM(LEADING FROM ‘ Abcd’) AS “TRIM” FROM DUMMY
Result: Abcd (Removes all the leading spaces from ‘ Abcd’)
Example: SELECT TRIM(LEADING ‘Ab’ FROM ‘Abcd’) AS “TRIM” FROM DUMMY
Result: cd (Removes the pattern ‘Ab’ from left side in ‘Abcd’)
UCASE - This convers the given input characters into upper case.
Syntax: UCASE(‘string’)
Example: SELECT UCASE(‘AbCD’) AS “UPPER CASE” FROM DUMMY
Result: ABCD (Upper case characters for ‘AbCD’)
UNICODE - This function returns the integer containing UNICODE code for the first character in the given string. If the first character is not a valid encoding then NULL is returned.
Syntax: UNICODE(string)
Example: SELECT UNICODE (‘[‘) “UNICODE” FROM DUMMY;
Result: 91 (UNICODE code for the character [)
UPPER-This converts all the characters in the given string to upper case. This is same as UCASE function.
Syntax: UPPER(string)
Example: SELECT UPPER(‘AbCD’) AS “UPPER CASE” FROM DUMMY
Result: ABCD (Upper case letters for string ‘AbCD’
No comments:
Post a Comment