MySQL intercepts the contents of a specified two strings (locate,substring)

If you need reprint, please specify the source http://blog.csdn.net/slimboy123/archive/2009/07/30/4394782.aspx

Today, my colleague, when using MySQL, needs to intercept the specified content in a string.
such as the existing string "[]aseabcd[12345]ddxabcdsx[]", to intercept "abcd[" and "abcd[" after the first "]" between the content "12345", of course, the content length is not fixed, can be "123456" or other strings.

When he asked me, my first reaction was to think of IndexOf, and later found out that MySQL did not indexof but locate.

After more than half an hour of trying, it is best to help him achieve this effect.

CREATE PROCEDURESp_str (inchP_strVARCHAR( -),/*Raw String*/inchP_begin_strVARCHAR( -),/*the starting string to match*/inchP_end_strVARCHAR( -))/*the ending string to match*/Out P_resultVARCHAR( -))/*return Results*/     notdeterministic SQL SECURITY definer COMMENT"'BEGINDECLAREM_lenINT DEFAULT 0;DECLAREM_indexINT DEFAULT 0; /*calculates the index position of the first matching string*/SelectLocate (P_BEGIN_STR,P_STR)+Char_length (P_BEGIN_STR) intoM_index;/*calculates the length of the first matched string*/SelectLocate (P_end_str,p_str,m_index) intoM_len;Select SUBSTRING(P_str,m_index,m_len-M_index) intoP_result;END;

Perform:
Call Sp_str (' []abcd[12345]aa[]ss ', ' abcd[', '] ', @result);

The return value @result 12345

Call Sp_str (' []abcd[sdww]aa[]ss ', ' abcd[', '] ', @result);
The return value @result to SDWW

If you do not use stored procedures, you can write directly to the SQL statement implementation:
Such as:

Select SUBSTRING(']abcd[12345]111[]', locate ('abcd[',']abcd[12345]111[]')+Char_length ('abcd['), locate (']',']abcd[12345]111[]', Char_length ('abcd['))-(SelectLocate'abcd[',']abcd[12345]111[]')+Char_length ('abcd[')))

The return value is 12345

A description of the functions of MySQL:

CHAR_LENGTH(str)　Returns str the length of the string. LOCATE(substr,str) POSITION(substr IN str) returns the substring substr at the str first occurrence of the string, if substr not str inside, to return 0 .　

Mysql> Select LOCATE (' Bar ', ' Foobarbar ');            -4mysql> Select LOCATE (' Xbar ', ' foobar ');  0

The function is multi-byte reliable.  

LOCATE(substr,str,pos)Returns the substring substr at the str first occurrence of the string, starting at the position pos . If substr not str inside, return 0 .

Mysql> Select LOCATE (' Bar ', ' Foobarbar ', 5);  7

This function is multi-byte reliable.

SUBSTRING(str,pos,len)　SUBSTRING(str FROM pos FOR len)　MID(str,pos,len)strreturns a len substring of characters from a string, starting at the position pos . FROMthe variant form used is the ANSI SQL92 syntax.

Mysql> Select SUBSTRING (' quadratically ', 5,6);  ' Ratica '

The function is multi-byte reliable.

SUBSTRING(str,pos)

MySQL intercepts the contents of a specified two strings (locate,substring)