(window.webpackJsonp=window.webpackJsonp||[]).push([[3278],{3686:function(t,a,s){"use strict";s.r(a);var e=s(31),r=Object(e.a)({},(function(){var t=this,a=t.$createElement,s=t._self._c||a;return s("ContentSlotsDistributor",{attrs:{"slot-key":t.$parent.slotKey}},[s("h1",{attrs:{id:"like-operator"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#like-operator"}},[t._v("#")]),t._v(" LIKE operator")]),t._v(" "),s("h2",{attrs:{id:"match-open-ended-pattern"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#match-open-ended-pattern"}},[t._v("#")]),t._v(" Match open-ended pattern")]),t._v(" "),s("p",[t._v("The "),s("code",[t._v("%")]),t._v(" wildcard appended to the beginning or end (or both) of a string will allow 0 or more of any character before the beginning or after the end of the pattern to match.")]),t._v(" "),s("p",[t._v("Using '%' in the middle will allow 0 or more characters between the two parts of the pattern to match.")]),t._v(" "),s("p",[t._v("We are going to use this Employees Table:")]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("Id")]),t._v(" "),s("th",[t._v("FName")]),t._v(" "),s("th",[t._v("LName")]),t._v(" "),s("th",[t._v("PhoneNumber")]),t._v(" "),s("th",[t._v("ManagerId")]),t._v(" "),s("th",[t._v("DepartmentId")]),t._v(" "),s("th",[t._v("Salary")]),t._v(" "),s("th",[t._v("Hire_date")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("1")]),t._v(" "),s("td",[t._v("John")]),t._v(" "),s("td",[t._v("Johnson")]),t._v(" "),s("td",[t._v("2468101214")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("400")]),t._v(" "),s("td",[t._v("23-03-2005")])]),t._v(" "),s("tr",[s("td",[t._v("2")]),t._v(" "),s("td",[t._v("Sophie")]),t._v(" "),s("td",[t._v("Amudsen")]),t._v(" "),s("td",[t._v("2479100211")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("400")]),t._v(" "),s("td",[t._v("11-01-2010")])]),t._v(" "),s("tr",[s("td",[t._v("3")]),t._v(" "),s("td",[t._v("Ronny")]),t._v(" "),s("td",[t._v("Smith")]),t._v(" "),s("td",[t._v("2462544026")]),t._v(" "),s("td",[t._v("2")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("600")]),t._v(" "),s("td",[t._v("06-08-2015")])]),t._v(" "),s("tr",[s("td",[t._v("4")]),t._v(" "),s("td",[t._v("Jon")]),t._v(" "),s("td",[t._v("Sanchez")]),t._v(" "),s("td",[t._v("2454124602")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("400")]),t._v(" "),s("td",[t._v("23-03-2005")])]),t._v(" "),s("tr",[s("td",[t._v("5")]),t._v(" "),s("td",[t._v("Hilde")]),t._v(" "),s("td",[t._v("Knag")]),t._v(" "),s("td",[t._v("2468021911")]),t._v(" "),s("td",[t._v("2")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("800")]),t._v(" "),s("td",[t._v("01-01-2000")])])])]),t._v(" "),s("p",[t._v("Following statement matches for all records having FName "),s("strong",[t._v("containing")]),t._v(" string 'on' from Employees Table.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employees "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" FName "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'%on%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("table",[s("thead",[s("tr",[s("th",[t._v("Id")]),t._v(" "),s("th",[t._v("FName")]),t._v(" "),s("th",[t._v("LName")]),t._v(" "),s("th",[t._v("PhoneNumber")]),t._v(" "),s("th",[t._v("ManagerId")]),t._v(" "),s("th",[t._v("DepartmentId")]),t._v(" "),s("th",[t._v("Salary")]),t._v(" "),s("th",[t._v("Hire_date")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("3")]),t._v(" "),s("td",[t._v("R"),s("strong",[t._v("on")]),t._v("ny")]),t._v(" "),s("td",[t._v("Smith")]),t._v(" "),s("td",[t._v("2462544026")]),t._v(" "),s("td",[t._v("2")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("600")]),t._v(" "),s("td",[t._v("06-08-2015")])]),t._v(" "),s("tr",[s("td",[t._v("4")]),t._v(" "),s("td",[t._v("J"),s("strong",[t._v("on")])]),t._v(" "),s("td",[t._v("Sanchez")]),t._v(" "),s("td",[t._v("2454124602")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("400")]),t._v(" "),s("td",[t._v("23-03-2005")])])])]),t._v(" "),s("p",[t._v("Following statement matches all records having PhoneNumber "),s("strong",[t._v("starting with")]),t._v(" string '246' from Employees.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employees "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" PhoneNumber "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'246%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("table",[s("thead",[s("tr",[s("th",[t._v("Id")]),t._v(" "),s("th",[t._v("FName")]),t._v(" "),s("th",[t._v("LName")]),t._v(" "),s("th",[t._v("PhoneNumber")]),t._v(" "),s("th",[t._v("ManagerId")]),t._v(" "),s("th",[t._v("DepartmentId")]),t._v(" "),s("th",[t._v("Salary")]),t._v(" "),s("th",[t._v("Hire_date")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("1")]),t._v(" "),s("td",[t._v("John")]),t._v(" "),s("td",[t._v("Johnson")]),t._v(" "),s("td",[s("strong",[t._v("246")]),t._v("8101214")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("400")]),t._v(" "),s("td",[t._v("23-03-2005")])]),t._v(" "),s("tr",[s("td",[t._v("3")]),t._v(" "),s("td",[t._v("Ronny")]),t._v(" "),s("td",[t._v("Smith")]),t._v(" "),s("td",[s("strong",[t._v("246")]),t._v("2544026")]),t._v(" "),s("td",[t._v("2")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("600")]),t._v(" "),s("td",[t._v("06-08-2015")])]),t._v(" "),s("tr",[s("td",[t._v("5")]),t._v(" "),s("td",[t._v("Hilde")]),t._v(" "),s("td",[t._v("Knag")]),t._v(" "),s("td",[s("strong",[t._v("246")]),t._v("8021911")]),t._v(" "),s("td",[t._v("2")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("800")]),t._v(" "),s("td",[t._v("01-01-2000")])])])]),t._v(" "),s("p",[t._v("Following statement matches all records having PhoneNumber "),s("strong",[t._v("ending with")]),t._v(" string '11' from Employees.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employees "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" PhoneNumber "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'%11'")]),t._v("\n\n")])])]),s("table",[s("thead",[s("tr",[s("th",[t._v("Id")]),t._v(" "),s("th",[t._v("FName")]),t._v(" "),s("th",[t._v("LName")]),t._v(" "),s("th",[t._v("PhoneNumber")]),t._v(" "),s("th",[t._v("ManagerId")]),t._v(" "),s("th",[t._v("DepartmentId")]),t._v(" "),s("th",[t._v("Salary")]),t._v(" "),s("th",[t._v("Hire_date")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("2")]),t._v(" "),s("td",[t._v("Sophie")]),t._v(" "),s("td",[t._v("Amudsen")]),t._v(" "),s("td",[t._v("24791002"),s("strong",[t._v("11")])]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("400")]),t._v(" "),s("td",[t._v("11-01-2010")])]),t._v(" "),s("tr",[s("td",[t._v("5")]),t._v(" "),s("td",[t._v("Hilde")]),t._v(" "),s("td",[t._v("Knag")]),t._v(" "),s("td",[t._v("24680219"),s("strong",[t._v("11")])]),t._v(" "),s("td",[t._v("2")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("800")]),t._v(" "),s("td",[t._v("01-01-2000")])])])]),t._v(" "),s("p",[t._v("All records where Fname "),s("strong",[t._v("3rd character")]),t._v(" is 'n' from Employees.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employees "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" FName "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'__n%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("p",[t._v("(two underscores are used before 'n' to skip first 2 characters)")]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("Id")]),t._v(" "),s("th",[t._v("FName")]),t._v(" "),s("th",[t._v("LName")]),t._v(" "),s("th",[t._v("PhoneNumber")]),t._v(" "),s("th",[t._v("ManagerId")]),t._v(" "),s("th",[t._v("DepartmentId")]),t._v(" "),s("th",[t._v("Salary")]),t._v(" "),s("th",[t._v("Hire_date")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("3")]),t._v(" "),s("td",[t._v("Ronny")]),t._v(" "),s("td",[t._v("Smith")]),t._v(" "),s("td",[t._v("2462544026")]),t._v(" "),s("td",[t._v("2")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("600")]),t._v(" "),s("td",[t._v("06-08-2015")])]),t._v(" "),s("tr",[s("td",[t._v("4")]),t._v(" "),s("td",[t._v("Jon")]),t._v(" "),s("td",[t._v("Sanchez")]),t._v(" "),s("td",[t._v("2454124602")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("400")]),t._v(" "),s("td",[t._v("23-03-2005")])])])]),t._v(" "),s("h2",{attrs:{id:"single-character-match"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#single-character-match"}},[t._v("#")]),t._v(" Single character match")]),t._v(" "),s("p",[t._v("To broaden the selections of a structured query language (SQL-SELECT) statement, wildcard characters, the percent sign (%) and the underscore (_), can be used.")]),t._v(" "),s("p",[t._v("The "),s("code",[t._v("_")]),t._v(" (underscore) character can be used as a wildcard for any single character in a pattern match.")]),t._v(" "),s("p",[t._v("Find all employees whose Fname start with 'j' and end with 'n' and has exactly 3 characters in Fname.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employees "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" FName "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'j_n'")]),t._v("\n\n")])])]),s("p",[s("code",[t._v("_")]),t._v(" (underscore) character can also be used more than once as a wild card to match patterns.")]),t._v(" "),s("p",[t._v('For example, this pattern would match "jon", "jan", "jen", etc.')]),t._v(" "),s("p",[t._v('These names will not be shown "jn","john","jordan", "justin", "jason", "julian", "jillian", "joann"  because in our query one underscore is used and it can skip exactly one character, so result must be of 3 character Fname.')]),t._v(" "),s("p",[t._v('For example, this pattern would match "LaSt", "LoSt", "HaLt", etc.')]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employees "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" FName "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'_A_T'")]),t._v("\n\n")])])]),s("h2",{attrs:{id:"escape-statement-in-the-like-query"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#escape-statement-in-the-like-query"}},[t._v("#")]),t._v(" ESCAPE statement in the LIKE-query")]),t._v(" "),s("p",[t._v("If you implement a text-search as "),s("code",[t._v("LIKE")]),t._v("-query, you usually do it like this:")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" \n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" T_Whatever \n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" SomeField "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" CONCAT"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token variable"}},[t._v("@in_SearchText")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" \n\n")])])]),s("p",[t._v("However, (apart from the fact that you shouldn't necessarely use "),s("code",[t._v("LIKE")]),t._v(' when you can use fulltext-search) this creates a problem when somebody inputs text like "50%" or "a_b".')]),t._v(" "),s("p",[t._v("So (instead of switching to fulltext-search), you can solve that problem using the "),s("code",[t._v("LIKE")]),t._v("-escape statement:")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" \n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" T_Whatever \n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" SomeField "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" CONCAT"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token variable"}},[t._v("@in_SearchText")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ESCAPE")]),t._v(" '\\'\n\n")])])]),s("p",[t._v("That means "),s("code",[t._v("\\")]),t._v(" will now be treated as ESCAPE character.\nThis means, you can now just prepend "),s("code",[t._v("\\")]),t._v(" to every character in the string you search, and the results will start to be correct, even when the user enters a special character like "),s("code",[t._v("%")]),t._v(" or "),s("code",[t._v("_")]),t._v(".")]),t._v(" "),s("p",[t._v("e.g.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[t._v("string stringToSearch "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v('"abc_def 50%"')]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nstring newString "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v('""')]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nforeach"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("char")]),t._v(" c "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("in")]),t._v(" stringToSearch"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" \n     newString "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token variable"}},[t._v('@"\\" + c;\n \nsqlCmd.Parameters.Add("')]),s("span",{pre:!0,attrs:{class:"token variable"}},[t._v("@in_SearchText")]),t._v('"'),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" newString"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v(" \n"),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v('// instead of sqlCmd.Parameters.Add("@in_SearchText", stringToSearch);')]),t._v("\n\n")])])]),s("p",[t._v("Note:\nThe above algorithm is for demonstration purposes only.\nIt will not work in cases where 1 grapheme consists out of several characters (utf-8).\ne.g. "),s("code",[t._v('string stringToSearch = "Les Mise\\u0301rables";')]),t._v("\nYou'll need to do this for each grapheme, not for each character.\nYou should not use the above algorithm if you're dealing with Asian/East-Asian/South-Asian languages. Or rather, if you want correct code to begin with, you should just do that for each graphemeCluster.")]),t._v(" "),s("p",[t._v("See also\n"),s("a",{attrs:{href:"https://stackoverflow.com/questions/1009689/reversestring-a-c-sharp-interview-question/36312251#36312251",target:"_blank",rel:"noopener noreferrer"}},[t._v("ReverseString, a C# interview-question"),s("OutboundLink")],1)]),t._v(" "),s("h2",{attrs:{id:"match-by-range-or-set"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#match-by-range-or-set"}},[t._v("#")]),t._v(" Match by range or set")]),t._v(" "),s("p",[t._v("Match any single character within the specified range (e.g.: "),s("code",[t._v("[a-f]")]),t._v(") or set (e.g.: "),s("code",[t._v("[abcdef]")]),t._v(").")]),t._v(" "),s("p",[t._v('This range pattern would match "gary" but not "mary":')]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employees "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" FName "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'[a-g]ary'")]),t._v("\n\n")])])]),s("p",[t._v('This set pattern would match "mary" but not "gary":')]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employees "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" Fname "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'[lmnop]ary'")]),t._v("\n\n")])])]),s("p",[t._v("The range or set can also be negated by appending the "),s("code",[t._v("^")]),t._v(" caret before the range or set:")]),t._v(" "),s("p",[t._v("This range pattern would "),s("strong",[t._v("not")]),t._v(' match "gary" but will match "mary":')]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employees "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" FName "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'[^a-g]ary'")]),t._v("\n\n")])])]),s("p",[t._v("This set pattern would "),s("strong",[t._v("not")]),t._v(' match "mary" but will match"gary":')]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employees "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" Fname "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'[^lmnop]ary'")]),t._v("\n\n")])])]),s("h2",{attrs:{id:"search-for-a-range-of-characters"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#search-for-a-range-of-characters"}},[t._v("#")]),t._v(" Search for a range of characters")]),t._v(" "),s("p",[t._v("Following statement matches all records having FName that starts with a letter from A to F from "),s("a",{attrs:{href:"http://stackoverflow.com/documentation/sql/280/example-databases/1014/employees-table#t=201603292355342567964",target:"_blank",rel:"noopener noreferrer"}},[t._v("Employees"),s("OutboundLink")],1),t._v(" Table.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employees "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" FName "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'[A-F]%'")]),t._v("\n\n")])])]),s("h2",{attrs:{id:"wildcard-characters"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#wildcard-characters"}},[t._v("#")]),t._v(" Wildcard characters")]),t._v(" "),s("p",[t._v("wildcard characters are used with the SQL LIKE operator.\nSQL wildcards are used to search for data within a table.")]),t._v(" "),s("p",[t._v("Wildcards in SQL are:%, _, [charlist], [^charlist]")]),t._v(" "),s("p",[s("strong",[t._v("%")]),t._v(" - A substitute for zero or more characters")]),t._v(" "),s("div",{staticClass:"language- extra-class"},[s("pre",{pre:!0,attrs:{class:"language-text"}},[s("code",[t._v("\n  Eg:  //selects all customers with a City starting with \"Lo\"\n        SELECT * FROM Customers\n        WHERE City LIKE 'Lo%';\n\n       //selects all customers with a City containing the pattern \"es\"\n      SELECT * FROM Customers\n       WHERE City LIKE '%es%';\n\n")])])]),s("p",[s("strong",[t._v("_")]),t._v(" - A substitute for a single character")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[t._v("Eg:"),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v('//selects all customers with a City starting with any character, followed by "erlin"')]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Customers\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" City "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'_erlin'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("p",[s("strong",[t._v("[charlist]")]),t._v(" - Sets and ranges of characters to match")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[t._v("Eg:"),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v('//selects all customers with a City starting with "a", "d", or "l"')]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Customers\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" City "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'[adl]%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v('//selects all customers with a City starting with "a", "d", or "l"')]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Customers\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" City "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'[a-c]%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("p",[s("strong",[t._v("[^charlist]")]),t._v(" - Matches only a character NOT specified within the brackets")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[t._v("Eg:"),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v('//selects all customers with a City starting with a character that is not "a", "p", or "l"')]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Customers\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" City "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'[^apl]%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("or")]),t._v("\n\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Customers\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" City "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'[apl]%'")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("and")]),t._v(" city "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("like")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'_%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("h2",{attrs:{id:"match-any-versus-all"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#match-any-versus-all"}},[t._v("#")]),t._v(" Match ANY versus ALL")]),t._v(" "),s("p",[t._v("Match any: "),s("br"),t._v("\nMust match at least one string. In this example the product type must be either 'electronics', 'books', or 'video'.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   purchase_table\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v("  product_type "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ANY")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'electronics'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'books'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'video'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("p",[t._v("Match all (must meet all requirements). "),s("br"),t._v("\nIn this example both 'united kingdom' "),s("strong",[t._v("and")]),t._v(" 'london' "),s("strong",[t._v("and")]),t._v(" 'eastern road' (including variations) must be matched.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   customer_table\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v("  full_address "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ALL")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'%united kingdom%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'%london%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'%eastern road%'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("p",[t._v("Negative selection: "),s("br"),t._v("\nUse ALL to exclude all items. "),s("br"),t._v("\nThis example yields all results where the product type is not 'electronics' and not 'books' and not 'video'.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   customer_table\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v("  product_type "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ALL")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'electronics'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'books'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v("'video'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("h4",{attrs:{id:"syntax"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#syntax"}},[t._v("#")]),t._v(" Syntax")]),t._v(" "),s("li",[s("p",[t._v("**Wild Card with % :**  SELECT * FROM [table]   WHERE [column_name] Like\n'%Value%'")]),t._v(" "),s("p",[t._v("**Wild Card with _ :**  SELECT * FROM [table]   WHERE [column_name] Like\n'V_n%'")]),t._v(" "),s("p",[t._v("**Wild Card with [charlist] :**  SELECT * FROM [table]   WHERE [column_name] Like\n'V[abc]n%'")])]),t._v(" "),s("h4",{attrs:{id:"remarks"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#remarks"}},[t._v("#")]),t._v(" Remarks")]),t._v(" "),s("p",[t._v("LIKE condition in WHERE clause is used to search for column values that matches the given pattern. Patterns are formed using following two wildcard characters")]),t._v(" "),s("ul",[s("li",[t._v("% (Percentage Symbol) - Used for representing zero or more characters")]),t._v(" "),s("li",[t._v("_ (Underscore) - Used for representing a single character")])])])}),[],!1,null,null,null);a.default=r.exports}}]);