MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL TIMESTAMPDIFF in MySQL MySQL Syntax Checker Sudo MySQL Secure Installation

MySQL String Function
2022-04-09 09:39:37

MySQL String Function

The string function is used to maintain and operate string values. This function modifies the string data as per function. We can do the concatenation, conversion, removal and replacing the string data by using the string function.

MySQL String Function Syntax

MySQL string function syntax gives below.

SELECT string_function("write string here..");

MySQL string function with position syntax shows below.

SELECT string_function("write string here..", position);

MySQL string function with several value syntax shows below.

SELECT string_function("string1", "string2");

MySQL String Functions

The following table shows string functions and its description.

FunctionDescription
ASCIIThe ASCII() function shows the ASCII value of the given character.
CHAR_LENGTHThis function displays the character length. The string contains the character and returns the length of the character, including spaces.
CONCATThe concat() function combines the two or more expressions of the data.
CONCAT_WSThe concat_ws() function is part of the MySQL string function that separates the two or more expressions of the data after concatenation.
FIELDThis function displays the index position of a value in a list.
FIND_IN_SETThe char_length() function displays the position of a value in a list.
INSERTThe INSERT function inserts a string at the required position with a given number of characters.
INSTRThe INSTR function displays the position of the first occurrence of a string with the other strings.
LCASEThe LCASE function uses to convert a string to lower-case. This function returns a string in lower-case.
UCASEThe UCASE function converts a string to upper-case and returns in capital string.
UPPERThe UPPER function is used to convert and shows a given string to the upper-case.
LEFTThe LEFT function extracts several characters from a left string.
LENGTHThe LENGTH function displays the length of a string in bytes format.
LOCATEThe LOCATE function displays the position of the first circumstance of a substring.
LOWERThe LOWER function is used to convert a string to lower-case.
LTRIMThe LTRIM function removes the initial spaces of the string.
MIDThe MID function extracts a substring of a string at any position.
POSITIONThe POSITION function displays the position of the first occurrence of a substring in a string.
REPEATThe REPEAT function repeats a string in the list of the data.
REPLACEThe REPLACE function replaces the circumstance of the substring in a string with a new substring.
REVERSEThe REVERSE function is used to reverses a string and returns the output.
RIGHTThe RIGHT function extracts several characters from the right of the string.
RTRIMThe RTRIM function uses to remove trailing spaces from a string.
SPACEThe SPACE function displays a string of the space characters
STRCMPThe STRCMP function compares two string values and returns the output.
SUBSTRThe SUBSTR function works to remove a substring from a starting string at any position.
TRIMThe TRIM function uses to remove leading and trail spaces from a string.

String Function Examples

MySQL string function works on the database information related string. You can find the length of the string, display string data in the required format, and operate string data as per applications. This function provides multiple operators to modify strings like concat, trim, reverse, and so on.

1. Example of the simple string function

This example shows basic operations about numbers and characters. Here, we have used the four-string functions and display the respective output.

mysql> select reverse("MySQL Tutorial Online") AS reverse,
      UCASE("MySQL Tutorial Online") AS upper_case,
      LCASE("MySQL Tutorial Online") AS lower_case,
      char_length("MySQL Tutorial Online") AS length;

Output

MySQL String Function

We can see four columns for four basic string functions with the "MySQL Tutorial Online" string value. The first column shows the reverse string and the second column displays the upper case string. The third column returns a lower case string, and the fourth column shows character length with space.

2. Example of the string function with multiple values

You can use several string functions in the MySQL query. The following function displays the left, right, and mid-value of the entire string data. Here, you can use the position function to get the place of the required character.

mysql> select left("MySQL Tutorial Online", 6) AS left_character,
       right("MySQL Tutorial Online", 6) AS right_character,
       mid("MySQL Tutorial Online", 7, 8) AS middle_character,
       position( "QL" IN "MySQL Tutorial Online") AS place_of_character;

Output

MySQL String Function

The above image shows the different characters of the string. The left function shows the first six characters, and the right function shows the last six characters of the string. The mid function shows the middle character of the string. The position function returns the available position of the first string in the second string.

3. Example of the string function with data

MySQL string function uses several functions to exchange, replace, and insert information. Here, you use the insert, replace, concat, and substring function with data. The "insert" function exchanges substring using position. MySQL substring function returns part of the string using position.

mysql> SELECT INSERT("MySQL Tutorial Online", 1, 5, "Database") AS insertdata,
     REPLACE("MySQL Tutorial Online", "mysql", "Java") AS exchange,
     CONCAT ("mysql", "online", "tutorial") AS combine,
     SUBSTRING("MySQL Tutorial Online", 7, 8) AS stringpart;

Output

MySQL String Function

Here, the MySQL output image returns four columns for four functions. The insert function exchanges substring from "MySQL" to "database" value. The replace function exchanges substring from "MySQL" to "java" value. The third function combines all string parts. The last function returns the required part of the string data.

4. Example of the string functions

This string function uses trim and RPAD and LPAD functions in the example. The trim function removes space on the left and right sides of the string. Here, the MySQL query uses RPAD and LPAD functions to add value to a string.

mysql> SELECT RTRIM("MySQL Tutorial Online               ") AS lefttrim,
    LTRIM("          MySQL Tutorial Online") AS righttrim,
    RPAD("MySQL Tutorial", 19, "learning") AS rightstring,
    LPAD("MySQL Tutorial", 19, "learning") AS leftstring;

Output

MySQL String Function

Here the trim function removes the right side of the space in the string. The ltrim function avoids left string space. The rpad and lpad functions insert specific data on the right and left sides, respectively.