How does sqlike work?
Fuzzy queries with like in standard SQL have two wildcards: % percent sign for any character and _ underscore for one character.
1. like ‘Mc%’ will search for all strings starting with the letter Mc.
2, like ‘%inger’ will search all strings ending with the letter inger.
3. like’%en%’ will search for all strings that contain the letter en in any position.
SQLLIKE operator syntax
‘_’: underscore, means 1 arbitrary character; such as…. .wherenamelike’_am’, means only the first arbitrary, and end with ‘am’, the result such as “Tam”, “Mam”, “Pam” and so on.
‘%’: percent sign, means any arbitrary character; such as …. .wherenamelike’%am’, that there can be any bit in front of it, as long as it ends with ‘am’, the result such as “abcam”, “bcdefgham”, “qwertyuiopam”, “am” and so on.
Reference: Baidu Encyclopedia – SQLLIKE
How to use like statement in sql
LIKE operator
The LIKE operator is used to search for a specified pattern in a column in the WHERE clause.
SQLLIKE operator syntax
SELECTcolumn_name(s)
FROMtable_name
WHEREcolumn_nameLIKEpattern
Example 1
Now we want to search for a pattern in the columns from the table ” Persons” table above to select people who live in cities starting with “N”:
We can use the following SELECT statement:
SELECT*FROMPersons
WHERECityLIKE’N%’
Hint: “%” can be used to define a wildcard character (a letter that’s missing from the pattern). letters).
Usage of like in SQL
LIKE is another command that is used in the WHERE clause. Basically, LIKE allows us to find out what we want based on a pattern. Comparatively speaking, with IN, we know exactly what we need; with BETWEEN, we are listing a range. LIKE syntax is as follows:
The following is a specific application:
1, like in the application of or: or means or, if the sql statement used in this connection character, it means that the results of the query as long as the conditions of one of them can be.
Example: SELECTt1 FROMtable_testWHEREt1LIKE’%a%’ORt1LIKE’%b%’
2, like in the application of and: and refers to and means that if the sql statement used in this connection character, it means that the query is to be in full compliance with the data of all the connection conditions in the sql statement. statement in all the connection conditions of the data.
Example: SELECTt1 FROMtable_testWHEREt1LIKE’%a%’AND t1LIKE’%b%’
3, like the splicing of multiple conditions: if the sql statement at the same time with commas to separate the multiple conditions, the results are based on the value of the actual table with the structure of the decision. If you want to get the desired result value in this way, you need to test repeatedly, otherwise the result will be biased.
Example: SELECTt1 FROMtable_testWHEREt1LIKE’%a%,%b%’
4, the application of like statement: used to filter some similar information or query the tree-structured table data, is a very good way.
5, like the use of: although like sometimes in the sql statement is still very convenient to find the results they want, but also in the use of sql performance issues should be taken into account.
Usage of SQLLike
1. %: means zero or more characters.
Can match any type and any length of characters, in some cases if Chinese, please use two percent sign (%%) to indicate.
For example: select*fromflow_userwhereusernamelike’%Wang%’;
will flow_user this table, column username in the name of the other “Wang ” in the column username.
Also, if you need to find records in the flow_user table that have both “Tang” and “Ying” in the username field, you can use the and condition,
< /p>
select*fromflow_userwhereusernamelike’%Ying%’andusernamelike’%Tang%’;
Then you can query all the records that contain both “Ying” and “Tang”. “Tang”, it doesn’t matter where “Ying” and “Tang” are located.
If you use select*fromflow_userwhereusernamelike’%Ying%Tang%’;
You can find out the content that contains “Ying and Tang” but you can’t find out the content that contains “Tang Ying”. “唐英”.
2, _: represents any single character. Match a single arbitrary character, it is often used to limit the character length of the expression:
select*fromflow_userwhereusernamelike’ying’;
Only can we find the “Wang Yingkun” such as username is three characters and the middle character is “ying”.
Another example: select*fromflow_userwhereusernamelike’Ying__’;
It can only find “Hero Point” where the username is three words and the first word is “Ying”.
3, []: represents one of the characters listed in parentheses (similar to a regular expression). Specify a character, string or range, requires the object matched for any of them.
select*fromflow_userwhereusernameLIKE ‘[Wang, Li, Zhang] Fei’;
Will find out if “Wang Fei” “Li Fei “”Zhang Fei” (instead of “Zhang Wang Li Fei”).
If there is a series of characters within [] (01234, abcde, or something like that) then it can be abbreviated as “0-4”, “a-e”:
select* flow_userwhereusernamelike’old[0-9]’;
Will find “old1”, “old2”, … …, “old 9”;
Oracle10g+ usage is:
select*fromflow_userwhereregexp_like( username,'[Zhang Wang Li] Fei’);
4. [^]: indicates a single character that is not listed in parentheses. Its take and [] the same, but it is required to match the object for the specified characters other than any one character.
select*fromflow_userwhereusernameLIKE'[^WANGLIZHANG]FEI’;
It will find out if it is not “WANG FEI” “LI FEI” “ZHANG FEI”. “, “Zhao Fei”, “Wu Fei”, etc. that are not “Wang Fei”, “Li Fei”, “Zhang Fei”.
Note: oraclelike does not support the regular, you can use the regular regexp_like support like
5, the query contains wildcard characters:
Because of the wildcard characters, resulting in the query of the special characters “% “, “_”, “[” statement can not be achieved normally, the special characters with “[]” bracketed by the normal query.
functionsqlencode(str)
str=replace(str,”[“,”[[]”)’This sentence must be at the top
str=replace(str,””,”[]”)
str=replace( str,”%”,”[%]”)
sqlencode=str
endfunction
Usage of like in sql
The LIKE operator can use the following two wildcards “%” and “-“. Where “%” represents zero or more characters. “-” represents one and only one character. What if you only remember that the first character of SALESMAN is S, the third character is L, and the fifth character is S?SQL>SELECTempno,ename,sal,job2FROMemp3WHEREjobLIKE’S_L_S% ‘;RESULTSEMPNOENAMESALJOB———————————————— 7499ALLEN1600SALESMAN7521WARD1250SALESMAN7654MARTIN1250SALESMAN7844TURNER1500SALESMANFrom the above query statement, it can be seen that by using different wildcards in the LIKE expression combinations of “%” and “-“, quite complex constraints can be constructed. In addition, the LIKE operator can help you simplify certain WHERE clauses. To display a list of all employees hired in 1981, for example, you can use this query statement.SQL>SELECTempno,ename,sal,hiredate2FROMemp3WHEREhiredateLIKE’%81’;Results EMPNOENAMESALHIREDATE———————————————–7499ALLEN160020-FEB-817521WARD125022-FEB-817566JONES297502-APR -817654martin125028-sep-817698blake285001-may-817782clark245009-jun-817839king500017-nov-817844turner150008-sep-817900james95003- DEC-817902FORD300003-DEC-81 has selected 11 rows.