Several common ways to determine whether a string exists in mysql

SQL determines whether a character is contained in a field. There are usually the following ways in mySQL

1. LIKE fuzzy query

LIKE is a keyword used for fuzzy search. Use % as a wildcard character to divide left mode, left mode, left mode

SELECT   'Diao Chan'  LIKE  '%Diao Chan%';

2. FIND_IN_SET function

FIND_IN_SET(str,strlist) ：str The string to be queried, strlist The fields to be queried, and the parameters are separated by ","; the function of this function is to query whether the field (strlist) contains the result of (str), and the return result is null or record.

SELECT   FIND_IN_SET('Diao Chan','Xi Shi, Zhao Jun, Diao Chan, Zhao Feiyan');

3. LOCATE function

LOCATE(substr,str,pos)Functions are functions in MySQL that find the first location of a string in another string. It returns the starting position of the target string in the source string, and if the target string does not exist, it returns 0. Compared with find_in_set, LOCATE is not constrained by delimiters. It is a string matching pattern, but it cannot directly return the record, so it needs to be manually judged.

Query string location

SELECT   LOCATE('Xi Shi','Xi Shi, Zhao Jun, Diao Chan, Zhao Feiyan');
SELECT   LOCATE('Diao Chan','Xi Shi, Zhao Jun, Diao Chan, Zhao Feiyan');
SELECT   LOCATE('Diao Chan','Xi Shi Zhaojun Diao Chan Zhao Feiyan');

Query records

SELECT  LOCATE('Diao Chan','Xi Shi, Zhao Jun, Diao Chan, Zhao Feiyan')&gt;0;

IV. INSTR function

INSTR(str,substr)I searches for the specified character (substr) in a string (str), and returns the position (index) of the specified character found. In the string str, the first position (index) of the string substr appears. The index is calculated from 1. If it is not found, it will directly return 0, and no negative number is returned.

SELECT  INSTR('Xi Shi, Zhao Jun, Diao Chan, Zhao Feiyan, Xi Shi,','Xi Shi');
SELECT  INSTR('Xi Shi, Zhao Jun, Diao Chan, Zhao Feiyan','Zhaojun') &gt; 0;

From the perspective of usage, the functions of INSTR and LOCATE are basically the same, but there are only very small differences in usage, mainly in the parameter list

Attachment: Simple way to get the number of occurrences of a string in a long string in SQL

--  =============================================
--  Author:        &lt;He Jianwei&gt;
--  Create date: &lt;2008-03-19&gt;
--  Description:    &lt;Get the number of occurrences of a string in a long string&gt;
--  =============================================
 ALTER   FUNCTION   [ dbo ] . [ fun_RepeatCharNum ]
(    
     @CodeString   varchar ( 2000 ),
     @RepeatString   varchar ( 200 )
)
 RETURNS   int  
 AS
 BEGIN  
     DECLARE
         @RepeatStringLen   int ,     -- Detect long strings of repeated characters
          @TempString   varchar ( 500 )     -- Temporary string(Total comparison@RepeatStringOne more is the length1)
     
     SET   @RepeatStringLen = LEN ( @RepeatString )
     SET   @TempString = Replicate ( ' | ' , @RepeatStringLen ) + ' | '

     RETURN   LEN ( REPLACE ( @CodeString , @RepeatString , @TempString )) - LEN ( @CodeString )
 END

This is the end of this article about several common ways to determine whether a string exists in mysql. For more related SQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!