MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Funtions and Operators / Question No: 234

What are string functions in MySQL?

Answer No: 234

String functions calculate string lengths, extract pieces of strings, search for substrings or replace them, perform lettercase conversion, and more.

LENGTH() and CHAR_LENGTH() functions

The LENGTH() and CHAR_LENGTH() functions determine string lengths in byte and character units, respectively. The values returned by the two functions will differ for strings that contain multi-byte characters. The following example shows this, using the latin1 single-byte character set and the ucs2 double-byte character set:

mysql> SET @s = CONVERT('MySQL' USING latin1);
mysql> SELECT LENGTH(@s), CHAR_LENGTH(@s);
+------------+-----------------+
| LENGTH(@s) | CHAR_LENGTH(@s) |
+------------+-----------------+
|          5 |               5 |
+------------+-----------------+


mysql> SET @s = CONVERT('MySQL' USING ucs2);
mysql> SELECT LENGTH(@s), CHAR_LENGTH(@s);
+------------+-----------------+
| LENGTH(@s) | CHAR_LENGTH(@s) |
+------------+-----------------+
|         10 |               5 |
+------------+-----------------+

CONCAT() and CONCAT_WS()

CONCAT() and CONCAT_WS() concatenate strings. CONCAT() concatenates all of its arguments, whereas CONCAT_WS() interprets its first argument as a separator to place between the following arguments:

mysql> SELECT CONCAT('aa','bb','cc','dd');
+-----------------------------+
| CONCAT('aa','bb','cc','dd') |
+-----------------------------+
| aabbccdd                    |
+-----------------------------+

mysql> SELECT CONCAT_WS('aa','bb','cc','dd');
+--------------------------------+
| CONCAT_WS('aa','bb','cc','dd') |
+--------------------------------+
| bbaaccaadd                     |
+--------------------------------+

The two functions also differ in their handling of NULL values. CONCAT() returns NULL if any of its arguments are null. CONCAT_WS() ignores NULL values:

mysql> SELECT CONCAT('/','a',NULL,'b'), CONCAT_WS('/','a',NULL,'b');
+--------------------------+-----------------------------+
| CONCAT('/','a',NULL,'b') | CONCAT_WS('/','a',NULL,'b') |
+--------------------------+-----------------------------+
| NULL                     | a/b                         |
+--------------------------+-----------------------------+

String Compare Funtion

The STRCMP() function compares two strings and returns 1, 0, or 1 if the first string is less than, equal to, or greater than the second string, respectively:

mysql> SELECT STRCMP('abc','def'), STRCMP('def','def'), STRCMP('def','abc');
+---------------------+---------------------+---------------------+
| STRCMP('abc','def') | STRCMP('def','def') | STRCMP('def','abc') |
+---------------------+---------------------+---------------------+
|                  -1 |                   0 |                   1 |
+---------------------+---------------------+---------------------+

Encryption Functions

MySQL encrypts passwords in the grant tables using the PASSWORD() function. This function should be considered for use only for managing MySQL accounts, not for general user applications. One reason for this is that applications often require reversible (two-way) encryption, and PASSWORD() performs irreversible (one-way) encryption. Another reason that applications should avoid reliance on PASSWORD() is that its implementation may change. (In fact, it did change in MySQL 4.1.0 and again in 4.1.1.)

For applications that work with data that must not be stored in unencrypted form, MySQL provides several pairs of functions that perform two-way encryption and decryption:

  • ENCODE() and DECODE()
  • DES_ENCRYPT() and DES_DECRYPT()
  • AES_ENCRYPT() and AES_DECRYPT()

Cryptographically, AES_ENCRYPT() and AES_DECRYPT() can be considered the most secure of the pairs. DES_ENCRYPT() and DES_DECRYPT() can be used if SSL support is enabled.

Related MySQL FAQs to the Above FAQ

What-is-MySQL-date-format-and-its-specifiers What is MySQL date format and its specifiers?

How-to-get-comma-separated-all-values-in-a-single-row-against-each-ID How to get comma separated all values in a single row against each ID?

How-to-concat-values-in-MySQL How to concat values in MySQL?

How-to-convert-time-zone-in-MySQL How to convert time zone in MySQL?

What-are-comparison-functions-in-MySQL What are comparison functions in MySQL

How-to-set-conditions-on-query-data-with-CASE-construct How to set conditions on query data with CASE construct?

How-to-set-control-conditions-on-query-data-with-IF-funtion How to set control conditions on query data with IF funtion?

What-are-mathematical-functions-in-MySQL What are mathematical functions in MySQL?

How-are-aggregate-functions-in-MySQL How are aggregate functions in MySQL?

What-are-temporal-or-date-time-functions-in-MySQL What are temporal or date time functions in MySQL?

What-are-NULL-value-related-functions-in-MySQL What are NULL value related functions in MySQL?

How-does-union-work-in-MySQL How does union work in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.