MySQL Common str function

Source: Internet
Author: User

Common String functions for MySQL

Organize yourself from official documents.

1.1     SUBSTR or SUBSTRING

SUBSTR (Str,pos), SUBSTR (str from POS), SUBSTR (Str,pos,len), SUBSTR (str from

POS for Len)

SUBSTR () is a synonym for SUBSTRING ().

SUBSTRING (Str,pos), SUBSTRING (str from POS), SUBSTRING (Str,pos,len),

SUBSTRING (str from POS for Len)

The forms without a Len argument return asubstring from string str starting at position pos.

The forms with a Len argument return asubstring len characters long from string str, starting at

Position Pos. The forms is use from Arestandard SQL syntax. It is also possible to use a negative

Value for POS. The Beginningof the substring is POS characters from the end of the

String, rather than the beginning. Anegative value may used-POS in any of the forms of

function.

For all forms of SUBSTRING (), the positionof the first character in the string from which the

SUBSTRING is-extracted is reckoned AS1.

Mysql> selectsubstring (' quadratically ', 5);

' Ratically '

mysql> SELECT SUBSTRING (' Foobarbar ' FROM4);

' Barbar '

Mysql> selectsubstring (' quadratically ', 5,6);

' Ratica '

mysql> SELECT SUBSTRING (' Sakila ',-3);

' Ila '

mysql> SELECT SUBSTRING (' Sakila ', -5,3);

' Aki '

mysql> SELECT SUBSTRING (' Sakila ' from-4for 2);

' Ki '

This function is multibyte safe.

If Len is less than 1,the result is the empty string.

1.2     Substring_index

Substring_index (Str,delim,count)

Returns the substring from string strbefore count occurrences of the delimiter Delim. If Count

is positive, everything to the left of Thefinal delimiter (counting from the left) is returned. If Count

is negative, everything to the right of Thefinal delimiter (counting from the right) is returned.

Substring_index () performs a case-sensitivematch when searching for Delim.

Mysql> selectsubstring_index (' www.mysql.com ', '. ', 2);

' Www.mysql '

Mysql> selectsubstring_index (' www.mysql.com ', '. ',-2);

' MySQL.com '

This function is multibyte safe.

1.3     Char_length

Char_length (str) returns the number of characters

Returns the length of the string str,measured in characters. A multibyte character counts as a

Single character. This means, astring containing five 2-byte characters, LENGTH () returns

Ten, whereas Char_length () returns 5.

1.4     LENGTH

LENGTH (str) Returns the number of bytes

Returns the length of the string str,measured in bytes. A multibyte character counts as multiple

bytes. This means a stringcontaining five 2-byte characters, LENGTH () returns, whereas

Char_length () returns 5.

mysql> SELECT LENGTH (' text ');


This article is from the "90SirDB" blog, be sure to keep this source http://90sirdb.blog.51cto.com/8713279/1941268

MySQL Common str function

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.