SQL STRING Functions

Function Parameters Result
UPPER (s) s= character string all letters are changed to UPPER CASE
LOWER (s) s= character string all letters are changed to LOWER CASE
INITCAP (s) s= character string Initial letter is changed to UPPER CASE and rest in LOWERCASE
CONCAT(s1, s2) s= character string Returns concatenated string
CHAR_LENGTH (s) s= character string Returns number of characters in a string
BIT_LENGTH (s) s= character string Returns number of bits in a string
REPLICATE (s, n) s= character string, n= number of times Returns the string specified number of times
TRANSLATE(s,f,t) s= character string, f= from-list, t=to-list Returns a string with each char in from-list replaced with corresponding char in to-list
INSERT(s,p,l,n_s) s= character string, p= position, l=length, n_s=new string inserts a substring at the specified position upto the specified number of character
FORMAT(s, d) s= numeric string, d=decimal places format s rounded to d decimal places
LOCATE(sub, s) s= numeric string, sub=sub string format s rounded to d decimal places
QUOTE(N\A) N\A= Not Applicable short form Escapes the arguments for use in SQL
REVERSE(s) s= character string Returns the strings with order of the character reversed
LPAD(s, l, ps) s= character string, l=length, ps= padded string Returns the string s, left padded with the string ps to a length of l characters
RPAD(s, l, ps) s= character string, l=length, ps= padded string Returns the string s, right padded with the string ps to a length of l characters
LTRIM(s) s= character string Returns the string s with leading space character removed
RTRIM(s) s= character string Returns the string s with trailing space character removed
ASCII(s) s= character string Returns ASCII value of first character of the string
BIN(s) s= character string Returns Binary value of the string
OCT(s) s= character string Returns Octal value of the string
HEX(s) s= character string Returns Hexadecimal value of the string
SPACE(n) s= character string Returns a string with n space character
STRCMP(s1, s2) s1, s2= character strings Returns zero if both strings are equal, -1 if first string is shorter than second string in order of sorting or else returns 1
RIGHT(s, l) s= character string, l=length of char Returns right most l character from string s
    Examples of String Function

SELECT SPACE(4); -> returns ” ”
SELECT RIGHT(‘Hollywood’, 3); -> returns ood
SELECT LTRIM(‘ Hella’); -> returns Hella
SELECT RPAD(‘Yo’,6, ‘ho’); -> Returns Yohoho
SELECT ASCII(‘9’); -> Returns 57