Define, syntax and an example with output for
CONCAT(string1, string2)Combines result from several different fields.
Syntax:
CONCAT(string1, string2)
Example:
SELECT CONCAT(first_name,last_name) FROM employee
WHERE first_name LIKE ‘b%’
Output:
Brad cooper
INITCAP(string)Returns a string with each word's first character in uppercase and the rest in lowercase.
Syntax:
INITCAP(character-expression)
Example:
select initcap (dept) from employee;
Output:
Analyst
LENGTH(string)Returns the length of the string. Also called as LEN()in sql server.
Syntax:
LENGTH(string)
Example:
select LENGTH (emp_name) from employee Where emp_id=’1’
Output:
6
LPAD(string, #,padding_char)Used to pad the string to left to length n characters.string_pad parameter is optional. If not specified, string will be padded spaces to the left-side of string.
Syntax:
lpad ('string', n [, 'string_pad')
Example:
lpad('tech', 7);
Output:
‘ tech’
RPAD(string, #,padding_char)Used to pad the string to right to length n characters.string_pad parameter is optional. If not specified, string will be padded spaces to the right-side of string.
Syntax:
rpad ('string', n [, 'string_pad')
Example:
rpad('tech', 7);
Output:
‘tech ’
LTRIM(string,searchString)Used to remove all white spaces from the beginning of the string.
Syntax:
Ltrim(string)
Example:
SELECT LTRIM(' Sample ');
Output:
‘sample ’
RTRIM(string,searchString)Used to remove all white spaces at the end of the string.
Syntax:
rtrim(string)
Example:
SELECT rTRIM(' Sample ');
Output:
‘ sample’
REPLACE(string,searchString,replacement)Used to replace the string1, having string2 with string3.
Syntax:
REPLACE(string1, string2, string3)
Example:
SELECT REPLACE(branch, 'st', 'saint') FROM company;
SUBSTR(string,start,length)Displays the string Starting with the th character in string and select the next characters.
Syntax:
SUBSTR(str,pos,len):
Example:
SELECT SUBSTR(‘San Diego,2,4);
Output:
‘an D’
UPPER(string)Returns a string in lowercase converted to uppercase
Syntax:
UPPER(string):
Example:
SELECT UPPER(‘sAnd’);
Output:
SAND
LOWER(string) Returns a string in uppercase converted to lowercase
Syntax:
LOWER(string):
Example:
SELECT LOWER(‘sANd’);
Output:
sand
