Analytics-Plus Help

 

MySQL STRING FUNCTIONS

The following table lists all the MySQL in-built String functions supported by ManageEngine Reports. Click on any function to know more about the same.

ASCIIBINBIT_LENGTHCHARCHAR_LENGTHCHARACTER_LENGTH
CONCATCONCAT_WSELTFIELDFIND_IN_SETFORMAT
HEXINSTRLEFTLENGTHLOCATELPAD
LTRIMMAKE_SETORDREPEATREPLACEREVERSE
RIGHTRPADRTRIMSOUNDEXSPACESUBSTRING
SUBSTRING_INDEXTRIMUNHEXUPPER

ASCII(string_arg):

Purpose :

The ASCII value of the first character of the given argument is returned.

Example :

Select ascii('ant') returns '97'

Note :

  • Returns '0' if argument is null.
  • The first character of the string can be any alphabet, number, symbol or special character

BIN(numeric_arg):

Purpose:

Returns the binary value of the given argument.

Example :

Select BIN('40') returns '101000'

Note :

  • Returns null if argument is null.
  • Returns 0 if argument is any non-numeric character.

BIT_LENGTH(string_arg):

Purpose:

Returns the value of the length of the string argument in bits.

Example :

Select BIT_LENGTH('AA') returns '16'

Note :

  • The argument can consist of any numeric, alphabetic or special character.
  • Returns 0 if argument is null.

CHAR(numeric_arg1, numeric_arg2, numeric_arg3,....):

Purpose:

Returns a String of characters formed from the code values of the ASCII code given in the arguments.

Example :

Select CHAR (97.4,97.5) returns 'ab'

Select CHAR ('97','X','98') returns 'a b' // Returns space for each non-numeric character in the argument.

Note :

  • Returns null if argument is null.
  • If the given ASCII is in decimals, then it returns the value of the closest whole number.
  • If the given ASCII is in decimals but is given as a string then the number after the decimal point is ignored and the code value of the number before the decimal alone is returned.

CHAR_LENGTH(string_arg):

Purpose:

Returns the number of characters present in the argument.

Example :

Select CHAR_LENGTH('aa1') returns '3'

Note :

  • The argument can consist of any numeric, alphabetic or special characters.
  • Returns 0 if argument is null.
  • Multibyte characters are counted as a single character.

CHARACTER_LENGTH(string_arg):

Purpose:

Returns the number of characters present in the argument.

Example :

Select CHARACTER_LENGTH('aa1') returns '3'

Note :

  • The argument can consist of any numeric, alphabetic or special characters.
  • Returns 0 if argument is null.
  • Multibyte characters are counted as a single character.

CONCAT(string_arg1, string_arg2, string_arg3,...):

Purpose:

Returns the concatenated string of the arguments.

Example :

Select CONCAT('red','rose') returns ' redrose'

Note :

  • The arguments can consist of any number, alphabet or special characters.
  • Returns null if all arguments are null.
  • Returns the non-null argument string if atleast one argument is not null and the rest of the arguments are null..

CONCAT_WS(char_arg, string_arg1, string_arg2,...):

Purpose:

Returns the concatenated string of the arguments with the first argument acting as the separator between the concatenated strings.

Example :

Select CONCAT_WS (',','red','rose','is','beautiful') returns 'red,rose,is,beautiful'

Note :

  • The arguments can consist of any number, alphabet or special characters.
  • Returns null if all arguments are null.
  • The seperator can be any symbol, letter, string, or number.
  • If the first argument is null, Returns the concatanated string of the rest of the arguments .
  • If the first argument is non-null and the rest of the arguments are null then the seperator is returned concatenated with itself (n-1) times, if 'n' is the number of arguments.

ELT(numeric_arg, string_arg1, string_arg2,...):

Purpose:

The value of the first argument gives the count of the argument to be returned from the rest of the arguments.

Example :

Select ELT ('2','red','rose','is','beautiful') returns 'rose'

Note :

  • The count of the argument to be returned is started from the second argument.
  • Returns null if first argument is null or the number of arguments is less than the count.
  • If the first argument is a non numeric character, then null is returned.

FIELD(string_arg, string_arg1, string_arg2,...):

Purpose:

The first argument string is checked with all the other arguments and the position of the argument where the first match is found is returned.

Example :

Select FIELD ('as','has','as','have') returns '2'

Note :

  • The position of the arguments starts from the second argument.
  • If the first argument or the rest of the arguments are null then '0' is returned.
  • Returns '0' if no match is found.

FIND_IN_SET(string_arg1, string(element1,element2,...)):

Purpose:

The first argument string is checked with the second argument, which is a set of strings seperated by commas, and the position of the first match in the set is returned.

Example :

Select FIND_IN_SET ('10','2,5,8,10') returns '4'

Note :

  • If either the first or the second argument is null, then '0' is returned.
  • If both the first and the second argument are null, then 'null' is returned.

FORMAT(numeric_arg,numeric_arg):

Purpose:

Rounds off the number given in the first argument to the number of decimals given in the second argument.

Example :

SELECT FORMAT (1.0001111,5) returns '1.00011'

Note :

  • If the second argument is 0 then there are no decimal value or decimal point in the returned number.
  • If the number has no decimals then zeros are appended to the number after the decimal point.

HEX(string_arg):

Purpose:

Returns the corresponding hexadecimal number for each character of the given string.

Example :

Select HEX('255') returns '323535'

Note :

  • If the given argument is null then null is returned.
  • If the given argument is given as a number then the hexadecimal number of the number as a whole is returned.

INSTR(string_arg, string_arg):

Purpose:

Returns the position of the first match of second string argument in the first argument.

Example :

Select INSTR ('impossible','possible') returns '3'

Note :

  • If both the arguments are null then '1' is returned.
  • If the second argument alone is null then '1' is returned.
  • If the first argument alone is null then '0' is returned.
  • If the second argument does not match with the first string then '0' is returned

LEFT(string_arg, numeric_arg):

Purpose:

From the first argument string the number of characters as that passed in the second argument is returned.

Example :

Select LEFT ('select','3') returns 'sel'

Note :

  • If null, '0' or any non numeric character is passed in the second argument then null is returned.
  • If the value of the second argument exceeds that of the first argument string then only the first argument string is fully returned

LENGTH(string_arg):

Purpose:

Returns the total number of bytes present in the string. The multibyte characters are counted as multiple bytes.

Example :

Select LENGTH ('advent123!$') returns '11'

Note :

  • Returns 0 if the argument is null.

LOCATE(string_arg):

Purpose:

Returns the position of the first string argument in the second string argument.

Example :

Select LOCATE ('net','adventnet') returns '7'

Note :

  • Returns 0 if the first argument is not present in the second argument.
  • Returns '0' if the second argument is null.
  • Returns 1 if the first argument is null.
  • Returns 1 if both the arguments are null.

LPAD(string_arg, numeric_arg, string_arg):

Purpose:

The third argument is prepended to the left of the first argument until the length given in the second argument is attained.

Example :

Select LPAD ('brother','9','hello') returns 'hebrother'

Note :

  • If the total length of the resultant string is more than the length to be attained, then the third string argument is cut off as the required length is attained.
  • Returns null if the third argument is null.
  • Returns null if the second argument is '0' or null.
  • If the length of the first argument exceeds the given count then the first argument is cut off upto the required result.
  • If the total length of the resultant string is less than the length to be attained, then the third string argument is repeated till the required length is attained.

LTRIM(string_arg):

Purpose:

Returns the argument with the blank spaces at the prefix of the string removed.

Example :

Select LTRIM (' ab cd') returns 'ab cd'

Note :

  • The space between the characters are not affected.

MAKE_SET(numeric_arg, string_arg1, string_arg2,...):

Purpose:

For the given bit in the first argument, the corresponding 'set' of arguments from the remaining arguments is returned.

Example :

Select MAKE_SET (3,'ab','cd','ef') returns 'ab,cd'

Note :

  • Returns null if the first argument is null or 0.

ORD(string_arg):

Purpose:

If the first character of the string argument is a multi-byte character, then the code calculated from the below formula is returned. 1st byte code + (2nd byte code * 256) + (3rd byte code * 256 * 256) + . . .

REPEAT(string_arg, numeric_arg):

Purpose:

The first string argument is returned repeatedly the number of times as that given in the second argument.

Example :

Select Repeat ('AA',5) returns 'AAAAAAAAAA'

Note :

  • Returns null if either or both the first argument and the second argument are null.

REPLACE(string_arg, string_arg, string_arg):

Purpose:

Replaces the string given in the second argument with the string given in the third argument from the first argument string.

Example :

select replace ('tention','ten','celebra') returns 'celebration'

Note :

  • Returns the first argument string if the second argument is null.
  • If the third argument is null, Returns the first argument string with the second argument string removed from it.
  • If the second and third argument are null then the first argument string is returned.

REVERSE(string_arg):

Purpose:

Returns the string argument in the reversed order of characters.

Example :

select reverse ('main') returns 'niam'

Note :

  • Returns null if the argument is null.

RIGHT(string_arg, numeric arg):

Purpose:

For the count passed in the second argument the first argument's characters from the right end is returned.

Example :

select RIGHT ('adventnet',3) returns 'net'

Note :

  • Returns null if either or both the first and second arguments are null.

RPAD(string_arg, numeric arg, string_arg):

Purpose:

The third argument is appended to the right of the first argument until the length of the second argument is acheived.

Example :

select RPAD ('abcdef',10,'ghijkl') returns 'abcdefghij'

Note :

  • If the total length of the resultant string is more than the length to be attained, then the resultant argument string is cut off as soon as the required length is attained.
  • Returns null if either the second or the third argument is null.
  • If the total length of the resultant string is less than the length to be attained, then the third string argument is repeated till the required length is attained.
  • Returns the repeated third argument if the first argument is null.

RTRIM(string_args):

Purpose:

Returns the argument with the blank spaces at the suffix of the string removed.

Example :

Select rtrim ('abcd ') returns 'abcd'

Note :

  • Returns null if the argument is null.

SOUNDEX(string_arg):

Purpose:

Returns the soundex string of the given argument. The soundex string is similar for same sounding strings.

Example :

Both Select soundex ('bye') and Select soundex ('boy') return 'b000'

SPACE(numeric_arg):

Purpose:

The argument value is returned as the number of space characters.

Example :

Select space(6) returns ' '

Note :

  • Returns null for non-numeric and negative arguments.

SUBSTRING(string_arg, numeric_arg):

Purpose:

Returns the substring formed by cutting off the string argument passed according to the needs.

Example :

Select substring ('adventnet',7) returns 'net'

Select substring ('adventnet' From 3) returns 'ventnet'

Select substring ('adventnet', 3,4 ) returns 'vent'

Select substring ('adventnet', -3) returns 'net'

<> Select substring ('adventnet', -7,4) returns 'vent'

Note :

  • Returns null when the numeric argument exceeds or is less than the length of the string argument.

SUBSTRING_INDEX(string_arg, string_arg, numeric_arg):

Purpose:

The second argument acts as the delimiter and the third argument is the count number. As soon as the match of the delimiter is found for the correct count in the first argument string, the rest of the string is cut off.

Example :

select substring_index ('how.are.you', '.', 2) returns 'how.are' // for positive count, limitation is done to the right of the delimiter.

select substring_index ('how.are.you', '.', -2) returns 'are.you' // for negaitive count, limitation is done to the left of the delimiter

Note :

  • If the count is null, zero or any non-numeric character then null is returned.
  • If the count is greater than the number of delimiters present in the string then the given string is returned without any change made to it.

TRIM(string_arg):

Purpose:

For the given specifiers the string argument is trimmed accordingly.

Example :

Select TRIM (' zoho ') returns 'zoho' // if no specifier is given, spaces before and after the string are trimmed

Select TRIM (leading '!' from '!!!!zoho!!!!') returns 'zoho!!!!' // if leading specifier is given then the prefix part is trimmed

Select TRIM (trailing '!' from '!!!!zoho!!!!') returns '!!!!zoho' // if trailing specifier is given then the suffix part is trimmed.

UNHEX(string_arg):

Purpose:

Returns the corresponding character for each pair of hexadecimal digits.

Example :

Select unhex ('21') returns '!'

Note :

  • Returns null when the argument value is a non hexadecimal number.

UPPER(string_arg):

Purpose:

Returns the argument string with all its alphabetic characters in Upper Case.

Example :

select upper ('AdVeNt') returns 'ADVENT'

Note :

  • The numeric and symbolic characters remain unchanged.

Share this post : FacebookTwitter