SQL - String Functions

ASCII(str)

• Syntax- ASCII ( character_expression )  
• Arguments- A string of type char or varchar
• Return types- A numerical value (int)
• Remarks- This function returns the value of the leftmost character of a function. ASCII stands for American Standard Code for Information Interchange. A value of 0 to 255 is returned. In the case of an empty string, 0 is returned. In the case of a NULL value, NULL is returned to the code. 

For Example:- SELECT ASCII (‘ABSC’) will return the value ‘65’, which is the ASCII code for the letter ‘A’.

BIN(N)

• Syntax- BIN (N)  
• Arguments- A decimal number ‘N’
• Return types- A binary value
• Remarks- This function returns the binary value of the decimal in the argument. 

For Example:- SELECT BIN (12) will return the value ‘1100’, which is the binary value.

BIT_LENGTH(str)

• Syntax- BIT_LENGTH(str)
• Arguments- A string of type char or varchar 
• Return types- A numerical value (int)
• Remarks- This function returns the length of the argument in bits. 

For Example- SELECT BIT_LENGTH (‘TEXT’) will return the value 32.

CHAR(N,... [USING charset_name])

• Syntax- CHAR(N,... [USING charset_name])
• Arguments- A numerical value (int)
• Return types- String
• Remarks- This function interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers.

For example:- SELECT CHAR(77,121,83,81,'76') will return the value 0x4D7953514C.

CHAR_LENGTH(str)

• Syntax- CHAR_LENGTH(str)
• Arguments- String
• Return types- A numerical value (int)
• Remarks- This function interprets the string and returns the length of the string in characters. The difference between the length () function and Char_Length() function is that the former counts the characters in bytes, whereas the Char_Length() function counts multi-byte characters as one. Thus for a string with three two-byte characters, length () will return the value of 6, whereas Char_Length() will return the value of 3.

For Example- SELECT CHAR_Length(“Eleven”) will return the value of 6.

CHARACTER_LENGTH(str)

• Syntax- CHARACTER_LENGTH(str)
• Arguments- String
• Return types- A numerical value (int)
• Remarks- This function is the same as CHAR_LENGTH(str)

CONCAT(str1,str2,...)

• Syntax- CONCAT(str1,str2,...)
• Arguments- Strings
• Return types- String
• Remarks- This function takes multiple strings as inputs and then concatenates the strings to get a single string value. Thus, if we input three strings, the result will be a concatenated single string value.

For Example:- SELECT CONCAT(‘one ‘, ‘line ‘, ‘below’). This will return the string with the text ‘one line below’.

CONCAT_WS(separator,str1,str2,...)

• Syntax- CONCAT_WS(separator,str1,str2,...)
• Arguments- Strings
• Return types- String
• Remarks- This function takes multiple strings as inputs and then concatenates the strings to get a single string value. A separator is passed as the first argument, and the separator is added between all the strings to be concatenated. Thus, if we input three strings, the result will be a concatenated single string value of the second and third-string with the first string acting as a separator between the two strings.

For example:- CONCAT_WS(',' , 'John’ , ‘Smith'). This will return the string with the text ‘John,Smith).

CONV(N,from_base,to_base)

• Syntax- CONV(N,from_base,to_base)
• Arguments- Integers
• Return types- String
• Remarks- This function transforms integers into newer bases. The two arguments for the bases are used to convert the number N into a string. From_base is the initial base, and To_base is the final base. If you write any value as NULL, the resultant output will be NULL. The argument N is interpreted as an integer but may be specified as an integer or a string. The minimum base is 2, and the maximum base is 36.

For example:- SELECT CONV('a',16,2). This will return the string with the text ‘1010’.

ELT(N,str1,str2,str3,...)

• Syntax- ELT(N,str1,str2,str3,...)
• Arguments- An integer as the first argument, and strings as all the other arguments.
• Return types- String
• Remarks- This function takes multiple strings as inputs along with an integer. If the integer is 1, the first string is given as the output. Similarly, if the integer is 2, the second string is given as the output. If you specify an integer value of less than 1 or more than the number of string inputs, a NULL value is returned.

For Example:- SELECT ELT(2,’abc’,’def’,’ghi’,’jkl’). The output will be the second string, i.e. ‘def’. 

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

• Syntax- EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
• Arguments- Bits, String
• Return types- String
• Remarks- This function uses the argument ‘bits’ to return the on bits and off bits. The number of bits to be returned is passed in the argument “Number_Of_Bits”. For Example, if you pass the value Bin(9) in bits function, the bits value bein ‘1001’.

For the below SQL query:-

Export_Set(9,’ON’,’OFF’, ‘,’,4)

The string returned is ON,OFF,OFF,ON

The reason is that 9 in bits is ‘1001’. The separator ‘,’ is passed in the arguments, and the number of bits is 4. By default, the number of bits is 64, and any higher value is automatically clipped to 64.

FIELD(str,str1,str2,str3,...)

• Syntax- FIELD(str,str1,str2,str3,...)
• Arguments- Strings
• Return types- Integer
• Remarks- It returns the index of the position str in the arguments. The arguments are counted starting from 1.  

For the below SQL query:-

Field(‘ab’,’abcd’,’ab’,’bfd’,’fdd’)

The returned value is ‘2’.

FIND_IN_SET(str,strlist)

• Syntax- FIND_IN_SET(str,strlist)
• Arguments- Strings
• Return types- Integer
• Remarks- It returns the index of the position str in the list of a single string.

For the below SQL query:-

FIND_IN_SET(‘a’,’b,a,c,d’)

The returned value is ‘2’.

FORMAT(X,D)

• Syntax- FORMAT(X,D)
• Arguments- Numbers
• Return types- String
• Remarks- It formats the number X to a decimal number rounded off to D decimal places. The output returned is in the form of a string.

For the below SQL query:-

FORMAT(12345.55553234,4)

The returned value is ‘12345.5555’.

HEX(N_or_S)

• Syntax- HEX(N_or_S)
• Arguments- Integer or string
• Return types- String
• Remarks- It converts the integer argument into a hexadecimal representation. If the argument passed is a string, then it converts each character into a hexadecimal representation.  

For the below SQL query:-

HEX(255)

The returned value is ‘FF’.

INSERT(str,pos,len,newstr)

• Syntax- INSERT(str,pos,len,newstr)
• Arguments- Strings and integers
• Return types- String
• Remarks- It converts the str argument into a new string by adding the string newstr with the len length and from pos position.

For the below SQL query:-

INSERT(‘abcdefgh’,2,3,’red’)

The returned value is ‘aredefgh’.

INSTR(str,substr)

• Syntax- INSTR(str,substr)
• Arguments- Strings
• Return types- Integer
• Remarks- It gives the position of the string substr in the string str.

For the below SQL query:-

INSTR(‘abcdefgh’,2,3,’red’)

The returned value is ‘aredefgh’.

LCASE(str)

This is the same as the function LOWER().

LEFT(str,len)

• Syntax- LEFT(str,len)
• Arguments- String, Integer
• Return types- String
• Remarks- It gives the first len characters of the string str.

For the below SQL query:-

LEFT(‘basketball’,5)

The returned value is ‘baske’.

LENGTH(str)

• Syntax- LENGTH(str)
• Arguments- String
• Return types- Integer
• Remarks- It gives the length of the string str.

For the below SQL query:-

LENGTH(‘abcdef’)

The returned value is 6.

LOAD_FILE(file_name)

• Syntax- LOAD_FILE(file_name)
• Arguments- String
• Return types- Strings
• Remarks- It reads the file and returns the strings in the file.

LOCATE(substr,str), LOCATE(substr,str,pos)

• Syntax- LOCATE(substr,str), LOCATE(substr,str,pos)
• Arguments- Strings, integer
• Return types- Integer
• Remarks- It returns the position of the substring substr in string str. In the case of the second syntax, it returns the position starting at position pos.  

For the below SQL query:-

LOCATE(‘abc’, ‘fgbabckf’)

The returned value is 4.

LOWER(str)

• Syntax- LOWER(str)
• Arguments- String
• Return types- String
• Remarks- It returns the string changed to lowercase characters.

For the below SQL query:-

LOWER(‘QUerY’)

The returned value is ‘query’.

LPAD(str,len,padstr)

• Syntax- LPAD(str,len,padstr)
• Arguments- Strings, integer
• Return types- String
• Remarks- It returns the string str left padded with the string padstr for len length.  

For the below SQL query:-

LPAD(‘eleven’,4,’two’)

The returned value is ‘twoeleven’.

LTRIM(str)

• Syntax- LTRIM(str)
• Arguments- String
• Return types- String
• Remarks- It returns the string after removing the blank spaces from the left side of the string.

For the below SQL query:-

LTRIM(‘ eleven’)

The returned value is ‘eleven’.

MAKE_SET(bits,str1,str2,...)

• Syntax- MAKE_SET(bits,str1,str2,...)
• Arguments- Strings, integer
• Return types- String
• Remarks- Gives the string value corresponding to the bits argument passed.

For the below SQL query:-

MAKE_SET(1,’a’,’b’,’c’)

The returned value is ‘a’.

MID(str,pos,len)

This is the same as the function SUBSTRING(str,pos,len).

OCT(N)

• Syntax- OCT(N)
• Arguments- BIGINT
• Return types- BIGINT
• Remarks- Gives the octal value of the argument N.

For the below SQL query:-

OCT(12)

The returned value is 14.

OCTET_LENGTH(str)

This is the same as the function LENGTH().

ORD(str)

• Syntax- ORD(str)
• Arguments- String
• Return types- Multi-byte Char
• Remarks- It returns the leftmost character of the string str.

For the below SQL query:-

ORD(‘2’)

The returned value is 50.

POSITION(substr IN str)

This is the same as the function LOCATE(substr,str).

QUOTE(str)

• Syntax- QUOTE(str)
• Arguments- String
• Return types- String
• Remarks- It returns the escaped string in quotes.

For the below SQL query:-

ORD(‘avv’)

The returned value is ‘avv’. 

This value is in quotations.   

expr REGEXP pattern

• Syntax- expr REGEXP pattern
• Arguments- Strings
• Return types- Bit value either ‘0’ or ‘1’.
• Remarks- If the expr argument matches the pattern argument, it returns 1, else it returns 0.

For the below SQL query:-

‘AER’ REGEXP ‘BCQ’

The returned value is 0.

REPEAT(str,count)

• Syntax- REPEAT(str,count)
• Arguments- String, Integer
• Return types- String
• Remarks- It repeats the string str a total of count times.

For the below SQL query:-

REPEAT(‘abc’,3)

The returned value is ‘abcabcabc’.

REVERSE(str)

• Syntax- REVERSE(str)
• Arguments- String
• Return types- String
• Remarks- It reverses the string str.

For the below SQL query:-

REVERSE(‘abc’)

The returned value is ‘cba’.

RIGHT(str,len)

• Syntax- RIGHT(str,len)
• Arguments- String, Integer
• Return types- String
• Remarks- It returns the rightmost len characters of the string str.

For the below SQL query:-

RIGHT(‘abcdef’,3)

The returned value is ‘def’.

RPAD(str,len,padstr)

• Syntax- RPAD(str,len,padstr)
• Arguments- String, Integer
• Return types- String
• Remarks- It returns the string str padded with the string padstr, and the total length of the string becomes len.

For the below SQL query:-

RPAD(‘abcdef’,9,’g’)

The returned value is ‘abcdefggg’.

RTRIM(str)

• Syntax- RTRIM(str)
• Arguments- String
• Return types- String
• Remarks- It returns the string str with all the spaces on the right removed.

For the below SQL query:-

RTRIM(‘abcdef   ’)

The returned value is ‘abcdef’.

SOUNDEX(str)

• Syntax- SOUNDEX(str)
• Arguments- String
• Return types- String
• Remarks- It returns the Soundex string for the argument str. Two similar sounding strings have the same Soundex Strings.

For the below SQL query:-

SOUNDEX(‘HELLO’)

The returned value is ‘H400’. 

expr1 SOUNDS LIKE expr2

This is the same as the function SOUNDEX(expr1) = SOUNDEX(expr2).

SPACE(N) 

• Syntax- SPACE(N)
• Arguments- Integer
• Return types- String
• Remarks- It returns the string consisting of blank characters of length N.

For the below SQL query:-

SPACE(5)

The returned value is ‘     ’.

STRCMP(str1, str2)

• Syntax- STRCMP(str1, str2)
• Arguments- Strings
• Return types- Integer
• Remarks- It returns 0 if the two arguments are equal. If first argument is smaller, it returns -1, and if the first argument is bigger, it returns 1.

For the below SQL query:-

STRCMP(‘abc’,’abc’)

The returned value is 0.

SUBSTRING(str,pos)

The above function returns a substring of the string str starting from pos position. When len argument is passed, it returns that length of the substring.

For example:-

SUBSTRING(‘abcdefghi’,4)

It returns the value ‘defghi’.

Also, for the below example:-

SUBSTRING(‘abcdefghi’,4,3)

It returns the value ‘def’.

SUBSTRING_INDEX(str,delim,count)

• Syntax- SUBSTRING_INDEX(str,delim,count)
• Arguments- Strings, Integer
• Return types- String
• Remarks- It returns the substring from the string str before the delimiter. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.

For the below SQL query:-

SUBSTRING_INDEX('www.mysql.com', '.', 2)

The returned value is ‘www.mysql’.

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

It removes the suffix and prefix remstr from the string str. If none of the arguments, LEADING or TRAILING is mentioned, it automatically assumes the argument BOTH.

For example:-

TRIM(‘ BAD  ‘)

It returns the value ‘BAD’.

Also, for example:-

TRIM(LEADING ‘a’ FROM ‘aaaaaBADaaa‘)

It returns the value ‘BADaaa’.

Also, for example:-

TRIM(BOTH ‘a’ FROM ‘aaaaaBADaaa‘)

It returns the value ‘BAD’.

UCASE(str)

This is the same as the function UPPER().

UNHEX(str)

• Syntax- UNHEX(str)
• Arguments- String
• Return types- String
• Remarks- It converts the hexadecimal string into the corresponding character values as a string.

For the below SQL query:-

UNHEX('4D7953514C')

The returned value is ‘SQL’.

Master front-end and back-end technologies and advanced aspects in our Post Graduate Program in Full Stack Web Development. Unleash your career as an expert full stack developer. Get in touch with us NOW!

Conclusion

The above SQL String functions will come in handy to prepare for interviews and learn the basics of SQL string functions. If you are looking to enhance your skills further and become job-ready, then we highly recommend Simplilearn’s Post Graduate Program in Full Stack Web Development. This course is designed in collaboration with Caltech CTME and will help you hone the required skills and make you job ready within 6 months.

If you have any doubts or queries, then feel free to post them in the comments section below. Our team will get back to you at the earliest.