String Operator in MySql

//////////////STRING OPERATOR///////////////
NameDescription
ASCII()Return numeric value of left-most character
BIN()Return a string representation of the argument
BIT_LENGTH()Return length of argument in bits
CHAR_LENGTH()Return number of characters in argument
CHAR()Return the character for each integer passed
CHARACTER_LENGTH()A synonym for CHAR_LENGTH()
CONCAT_WS()Return concatenate with separator
CONCAT()Return concatenated string
ELT()Return string at index number
EXPORT_SET()Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
FIELD()Return the index (position) of the first argument in the subsequent arguments
FIND_IN_SET()Return the index position of the first argument within the second argument
FORMAT()Return a number formatted to specified number of decimal places
HEX()Return a hexadecimal representation of a decimal or string value
INSERT()Insert a substring at the specified position up to the specified number of characters
INSTR()Return the index of the first occurrence of substring
LCASE()Synonym for LOWER()
LEFT()Return the leftmost number of characters as specified
LENGTH()Return the length of a string in bytes
LIKESimple pattern matching
LOAD_FILE()Load the named file
LOCATE()Return the position of the first occurrence of substring
LOWER()Return the argument in lowercase
LPAD()Return the string argument, left-padded with the specified string
LTRIM()Remove leading spaces
MAKE_SET()Return a set of comma-separated strings that have the corresponding bit in bits set
MATCHPerform full-text search
MID()Return a substring starting from the specified position
NOT LIKENegation of simple pattern matching
NOT REGEXPNegation of REGEXP
OCTET_LENGTH()A synonym for LENGTH()
ORD()Return character code for leftmost character of the argument
POSITION()A synonym for LOCATE()
QUOTE()Escape the argument for use in an SQL statement
REGEXPPattern matching using regular expressions
REPEAT()Repeat a string the specified number of times
REPLACE()Replace occurrences of a specified string
REVERSE()Reverse the characters in a string
RIGHT()Return the specified rightmost number of characters
RLIKESynonym for REGEXP
RPAD()Append string the specified number of times
RTRIM()Remove trailing spaces
SOUNDEX()Return a soundex string
SOUNDS LIKE(v4.1.0)Compare sounds
SPACE()Return a string of the specified number of spaces
STRCMP()Compare two strings
SUBSTR()Return the substring as specified
SUBSTRING_INDEX()Return a substring from a string before the specified number of occurrences of the delimiter
SUBSTRING()Return the substring as specified
TRIM()Remove leading and trailing spaces
UCASE()Synonym for UPPER()
UNHEX()Convert each pair of hexadecimal digits to a character
UPPER()Convert to uppercase




  • ASCII (str)The ASCII(str) function returns the ASCII code value of the leftmost character of the String str. Returns 0 if str is the empty string. Returns NULL if str is NULL. Here are the some example of the ASCII(str) function:
         
    mysql> SELECT ASCII('0');
    -> 48
    mysql> SELECT ASCII(0);
    -> 48
    mysql> SELECT ASCII('d');
    -> 100
  • BIN (N)The BIN string function return a string value representing of the binary value of N, where N is a longlong(BIGINT) number. This function is equivalent to CONV(N, 10 , 0). If the function return the null then N is null. Here are the some example of the BIN(N) function:
         
     mysql> SELECT BIN(5);
    -> '101'
  • BIT_LENGTH (str)The BIT_LENGTH(str) function return the String str length in bits . Here are the some example of the BIT_LENGTH(str) function:
         
    mysql> SELECT BIT_LENGTH('a');
    -> 8
  • CHAR(N,... [USING charset_name] )The CHAR(N,... [USING charset_name] ) function  return a string consisting the character and given the integer value. This function skipped the NULL values. Here are the some example of the CHAR(N,... [USING charset_name] ) function:   
    Example:
           
    mysql> SELECT CHAR(77,121,83,81,'76');
    -> 'MySQL'
    mysql> SELECT CHAR(66,66.3,'66.3');
    -> 'BBB'
  • CHAR_LENGTH(str)The CHAR_LENGTH(str) function returns String str lengths that is measured in characters. But in this function a multi-byte character counts as single character such as a string contains 5 two-byte characters, then LENGTH() function returns 10, but the CHAR_LENGTH() returns 5.
           
  • CHARACTER_LENGTH(str)
    This function is same as CHAR_LENGTH().
          
  • CONCAT(str1, str2..)The CONCAT(str1, str2….) function can have one or more arguments and its returns a string that is the result of concatenating the arguments. In this function all arguments are non-binary strings then the result is also non-binary string but if any argument is binary string then result is binary string. And a numeric argument is converted to its equivalent binary string form. But if any argument is NULL then it also returns NULL. 
    Example:    
          
    mysql> SELECT CONCAT('In', 'd', 'ia');
    -> 'India'
    mysql> SELECT CONCAT('my', NULL, 'ql');
    -> NULL
    mysql> SELECT CONCAT(10.3);
    -> '10.3'
           
  • CONCAT_WS(separator str1, str2,....) The CONCAT_WS() means CONCAT With Separator. The first argument is treated as a separator for the rest of the arguments and it is added between the strings for concatenating. If the separator is NULL then the result is NULL.
    Example:
          
    mysql> SELECT CONCAT_WS(',', ' Title', 'First name', 'Last Name');
    -> 'Title, First name, Last Name'
    mysql> SELECT CONCAT_WS(',', 'First name', NULL, 'Last Name');
    -> 'First name, Last Name'
            
  • CONV (N, from_base, to_base)The CONV (N, from_base, to_base) function is used to convert the number between different number bases. This function returns a String that is representation of number N. It convert the number N from base from_base to base to_base. But it returns NULL if any argument is NULL. In this function argument N is interpreted as an integer, but it can be specified as a string or as a integer also. The minimum and the maximum base is 2 and 36 respectively. But if to_base is negative then N is treated as a signed number else it is treated as a unsigned number.      Example:
           
    mysql> SELECT CONV('a',10,2);
    -> '0'
    mysql> SELECT CONV('6E',10,5);
    -> '11'
    mysql> SELECT CONV(-17,9,-25);
    -> '-G'
    mysql> SELECT CONV(10+'10'+'10'+0xa,10,5);
    -> '130'
           
  • ELT(N,str1,str2,str3,...)The ELT(N, str1, str2, str3,..) function returns str1 if N=1 and str2 if N=2 and so on. But it returns NULL if N is greater than the total number of arguments or less than 1. Here are some example of the ELT(N, str1, str2, str3,..) function:     
    Example:
          
    mysql> SELECT ELT(1, '9', '10', '11', '12');
    -> '9'
    mysql> SELECT ELT(4, '9', '10', '11', '12');
    -> '12'
        
  • EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
    The EXPORT_SET(bits, on, off[, separator[,number_of_bits]]) function returns a string for a every bit set in the value bits, then you get the on String but for every reset bit you can get the off string. In this function Bits in bits are examined from right to left but Strings are concatenate to the result from left to right. The number of bits are calculated by given number_of_bits that’s default value is 64.    Example:
          
    mysql> SELECT EXPORT_SET(1,'Y','N',',',2);
    -> 'Y,N'
    mysql> SELECT EXPORT_SET(6,'1','0',',',2);
    -> '0,1'
  • FIELD(str,str1,str2,str3,...)The FIELD(str,str1,str2,str3,....) function is used to find the index position of str in the arguments str1,str2,str3. In other words it returns the index position of str in the arguments. It returns 0 if str is not available in the arguments. If str is NULL then return value is 0 because NULL fails equality comparison with any value. 
    Example:
            
    mysql> SELECT FIELD ('AA', 'BB', 'AA', 'CC');
    -> 2
    mysql> SELECT FIELD ('AA', 'BB', 'CC', 'DD');
    -> 0
         
  • FIND_IN_SET(str,strlist)
    The FIND_IN_SET(str, strlist) function returns a value in the range of 1 to N. This function find the String str in the substring of String list strlist and return the index value. This String list have many substrings that is separated by ‘,’ characters. This function returns 0 when str is not available in stringlist or string list is the empty string.  
    Example:
             
    mysql> SELECT FIND_IN_SET('2', '1,2,3,4');
    -> 2
        
  • FORMAT(X,D)The FORMAT(X,D) function formats the number X like #,###,###.## and rounded the decimal places to D then returns the string as a result. But if D is 0 then the result don’t have fractional part. Some examples of the FORMAT(X, D) function are given below :Example:
    mysql> SELECT FORMAT(1235.14687, 3);
    -> 1,235.147
    mysql> SELECT FORMAT(145678.1,2);
    -> 145,678.10
    mysql> SELECT FORMAT(24567.1,0);
    -> 24567
           
  • HEX(N_or_S)In HEX(N_or_S) function N_or_S is a number then this function returns a string that is representation of hexadecimal value of N, where is a longlon(BIGINT) number. But if N_or_S is a string, then it returns a string hexadecimal representation of N_or_S where each character in N_or_S is converted to two hexadecimal digits. Here are some example of the HEX(N_or_S) function are given below:Example:
          
    mysql> SELECT HEX(10);
    -> 'A'
    mysql> SELECT HEX( 'abd');
    -> 616264
         
  • INSERT(str,pos,len,newstr)The INSERT(str, pos, len, newstr) function is used to replace some part or whole String of String strwith String newstr from beginning at position pos and len character long. This function returns the String str if pos is not within the length of the string. It returns NULL if any argument is NULL.
    Example:
         
    mysql> SELECT INSERT('roseindia',2,3,'net');
    -> rnetindia
    mysql> SELECT INSERT('roseindia',-1,3,'net');
    -> roseindia
    mysql> SELECT INSERT('roseindia',3,100,'net');
    ->ronet
          
  • INSTR(str,substr)The INSTR(str, substr) function is used to return the position of first occurrence of substrSubString in str String. Here are some example of the INSTR(str,substr) function:
    Example:
         
    mysql> SELECT INSTR('roseindia','e');
    -> '4'
    mysql> SELECT INSTR('xe', 'roseindia');
    -> '0'
  • LOWER(str)
    The LOWER(str) function return the String str. And in this String all the characters are changed in the lowercase.
        
    mysql> SELECT LOWER('ROSEINDIA');
    ->'roseindia'
        
  • LCASE(str)The LCASE(str) function is same as LOWER() function
         
  • LEFT(str,len)The LEFT(str, len) function returns the leftmost len characters from the String str. Here are the some example of the LEFT(str, len) function:
    Example:
         
    mysql> SELECT LEFT('roseindia', 4);
    -> 'rose'
  • LENGTH(str)The LENGTH(str) function returns the length of the String str in bytes. Here are the some example of the LENGTH(str) function:Example:
         
    mysql> SELECT LENGTH("roseindia");
    ->'9'
        
  • LOAD_FILE(file_name) The LOAD_FILE(file_name) function is used to read the file and this function returns the content of file as a string. For using it the file must be located on the server host., you must specify the full path of the file. But for using this function you must have FILE privilege and the file size is less than max_allowed_packet bytes.
        
    mysql> SELECT LOAD_FILE('C:/MySQL/MySQL Server 5.0/data');
         
  • LOCATE(substr,str), LOCATE(substr,str,pos)The LOCATE(substr,str) function is same as INSTR(str, substr). LOCATE(substr,str,pos) function is also same but its just start the to find first occurrence of substr in String str from position pos. These functions returns 0 if substr is not in String str. Here are the some example of the LOCATE(substr,str), LOCATE(substr,str,pos) function:Example:
         
    mysql> SELECT LOCATE('in','roseindia');
    -> 5
    mysql> SELECT LOCATE('xin','roseindia');
    -> 0
    mysql> SELECT LOCATE('d','roseindia',4);
    -> 7
               
  • LPAD(str,len,padstr)The LPAD(str, len, padstr) function returns the string str that is left padded with padstr string for length of len characters. But string str is longer than len characters then return value is shortend to len characters. Example:
          
    mysql> SELECT LPAD('hello',7,'??');
    ->??hello
    mysql> SELECT LPAD('hello',1,'??');
    -> h
          
  • LTRIM(str)The LTRIM(str) function returns the string str with leading space characters removed. Here are the some example of the LTRIM(str) function:
    Example:
          
    mysql> SELECT LTRIM('      roseindia');
    -> 'roseindia'
          
  • MAKE_SET(bits,str1,str2,...)The MAKE_SET(bits, str1, str2,..) function is returns a set value consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result. Here are the some example of  the MAKE_SET(bits, str1, str2,..) function:
    Example:
         
    mysql> SELECT MAKE_SET(2,'a', 'b','c','d');
    -> 'b'
    mysql> SELECT MAKE_SET(1|2,'hello','nice','comp');
    -> 'hello,nice'
    mysql> SELECT MAKE_SET(1|4,'good','nice',null,'by');
    -> 'good'
    mysql> SELECT MAKE_SET(0, '1','2', '3', '4');
    ->''
         
  • MID(str,pos,len)The MID(str, pos, len) function is same as SUBSTRING(str,pos,len)
           
  • OCT(N)The OCT(N) function is used to return a string representation of octal value of N, here N is a longlong (BIGINT) number
    Example:
          
    mysql> SELECT OCT(12);
    -> '14'
         
  • OCTET_LENGTH(str)The OCTET_LENGTH(str) function is same as LENGTH().
        
  • POSITION(substr IN str)
    The POSITION(substr IN str) function is same as LOCATE (substr, str).
         
  • REPEAT (str, count)The REPEAT (str, count) function returns a string that consist a String str repeated of count times. But if count time is less than 1 than it returns an empty string.
        
    mysql> SELECT REPEAT('Rose', 3);
    ->'RoseRoseRose'
         
  • REPLACE (str, from_str, to_str)The REPLACE (str, from_str, to_str) function returns the String str and in this String all occurrences of the String from_str is replaced by the String to_str. This function can perform a case-sensitive match when searching for from_str.
        
    mysql> SELECT REPLACE ('www.roseindia.net', 'w', 'W');
    ->'WWW.roseindia.net';
         
  • REVERSE(str)The REVERSE(str) function is used to return the reverse of String str.
    Example:
         
    mysql> SELECT REVERSE('123');
    -> '321'
          
  • RIGHT(str, len)
    The RIGET(str, len) function returns the rightmost len characters from the String str. It return NULL if any argument is NULL.
    Example :
        
    mysql> SELECT RIGHT ('Roseindia', 5);
    ->'india'
          
  • RPAD(str, len, padstr)
    The RPAD(str, len, padstr) function returns the string str that is right padded with padstr string for length of len characters. But string str is longer than len characters then return value is shortend to len characters.
    Example :
         
    mysql> SELECT RPAD ('rose', 7, '?');
    ->'rose???'
           
  • RTRIM(str)The RTRIM(str) function returns the String str with trailing space characters removed.
    Example :
         
    mysql> SELECT RTRIM ('rose      ');
    ->'rose'
          
  • SPACE(N)The SPACE(N) function returns a String that consist of N space characters.
    Example :
         
    mysql> SELECT SPACE(5);
    -> '     '
         
  • SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len) The first two functions SUBSTRING(str,pos) and SUBSTRING(str FROM pos) return a substring from str String that is started at position pos. And the other two functions SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len) return a substring, that’s length is len characters, from String str and its started at position pos.
    Example :
           
    mysql> SELECT SUBSTRING('RoseIndia',5);
    -> 'India'
    mysql> SELECT SUBSTRING('RoseIndia' FROM 5);
    -> 'India'
    mysql> SELECT SUBSTRING('RoseIndia',5,3);
    -> 'Ind'
          
  • UPPER(str)The UPPER(str) function return the String str. And in this string all the characters are changed in the uppercase.
    Example :
       
    mysql> SELECT UPPER('roseindia');
    ->'ROSEINDIA'

Comments

Post a Comment

Popular posts from this blog

Error : DependencyManagement.dependencies.dependency.(groupId:artifactId:type:classifier)' must be unique: com.adobe.aem:uber-jar:jar:apis -> version 6.3.0 vs 6.4.0

Image
I got above error after adding dependency in Maven POM xml file placed under newly created AEM arch type and running command  mvn -PautoInstallPackage clean install . Error : DependencyManagement.dependencies.dependency.(groupId:artifactId:type:classifier)' must be unique: com.adobe.aem:uber-jar:jar:apis -> version 6.3.0 vs 6.4.0 After google and gone through few article I got command ( mvn -PautoInstallPackage -Padobe-public clean install) . Once I run this command this issue with jar compatibility was resolved. Happy Learning!!!
Read more

ERROR Exception while handling event Sitecore.Eventing.Remote.PublishEndRemoteEventException: System.AggregateExceptionMessage: One or more exceptions occurred while processing the subscribers to the 'publish:end:remote'

I was getting below error while working with cache management on CM and CD server. Actually my code was working fine on CM server but I was not able to access or clear cache object on CD server because of wrong event selection.  PID[18108] Error       ManagedPoolThread #3 16:24:16 ERROR Exception while handling event Sitecore.Eventing.Remote.PublishEndRemoteEventException: System.AggregateExceptionMessage: One or more exceptions occurred while processing the subscribers to the 'publish:end:remote' event.Source: Sitecore.Kernelat Sitecore.Events.Event.EventSubscribers.RaiseEvent(String eventName, Object[] parameters, EventResult result)at Sitecore.Events.Event.RaiseEvent(String eventName, Object[] parameters)at Sitecore.Eventing.Subscription`1.Invoke(Object instance, EventContext context)at Sitecore.Eventing.EventProvider.RaiseEvent(Object event, Type eventType, EventContext context) Above error comes due to wrong event args access in CD server. T...
Read more

Error : Sorry, there was an error : unauthorized_client in Sitecore 9.1 login

Image
After adding a new binding from IIS on my sitecore 9.1 instance I was getting unauthorized client error while login with newly added binding URL. To fix this issue I added AllowedCorsOriginsGroup in xml file placed at File Name :  Sitecore.IdentityServer.Host.xml Location : C:\inetpub\wwwroot\9Dot1.identityserver\Config\production After adding field tag AllowedCorsOriginsGroup in  Sitecore.IdentityServer.Host.xml file I was able to login properly with newly added URL. Also you can fix this issue by placing a pipe operator beside of old AllowedCorsOriginsGroup as below. <AllowedCorsOriginsGroup1>http://9Dot1.sc | http://jss.9Dot1.sc</AllowedCorsOriginsGroup1 Happy Learning!!!
Read more