{"id":9624,"date":"2021-07-06T14:10:03","date_gmt":"2021-07-06T12:10:03","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=9624"},"modified":"2021-07-06T14:10:03","modified_gmt":"2021-07-06T12:10:03","slug":"sql-operateur-de-comparaison-like","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=9624","title":{"rendered":"SQL : op\u00e9rateur de comparaison LIKE"},"content":{"rendered":"<p style=\"text-align: justify;\">Dans une <a href=\"https:\/\/www.auditsi.eu\/?p=6899\">clause <em>WHERE<\/em><\/a>, l&#8217;op\u00e9rateur <em>LIKE<\/em> permet d&#8217;effectuer une comparaison partielle entre le contenu d&#8217;un champ de donn\u00e9es et une cha\u00eene de caract\u00e8res. LIKE est un <a href=\"https:\/\/www.auditsi.eu\/?p=6979\">op\u00e9rateur de comparaison<\/a> sp\u00e9cifique au langage de requ\u00eate SQL.<\/p>\n<p style=\"text-align: justify;\">Si la comparaison est r\u00e9alis\u00e9e, elle renvoie <em>True<\/em> sinon <em>False<\/em>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9625\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2021\/07\/Logo-ACCESS.png\" alt=\"Logo ACCESS\" width=\"664\" height=\"667\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2021\/07\/Logo-ACCESS.png 772w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2021\/07\/Logo-ACCESS-300x300.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2021\/07\/Logo-ACCESS-150x150.png 150w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2021\/07\/Logo-ACCESS-768x771.png 768w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2021\/07\/Logo-ACCESS-730x733.png 730w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2021\/07\/Logo-ACCESS-120x120.png 120w\" sizes=\"auto, (max-width: 664px) 100vw, 664px\" \/><\/p>\n<p style=\"text-align: justify;\">L&#8217;utilisation de l&#8217;op\u00e9rateur <em>LIKE<\/em> conjointement aux jokers (*, ?) permet une grande diversit\u00e9 de comparaisons. Les jokers remplacent n&#8217;importe quel caract\u00e8re (? remplace un caract\u00e8re, * en remplace plusieurs).<\/p>\n<p>Contexte d&#8217;utilisation :<\/p>\n<p style=\"text-align: center;\">WHERE champ LIKE comparaison<\/p>\n<p>Exemples de comparaisons avec des jokers :<\/p>\n<ul>\n<li>LIKE &#8220;P*&#8221; : cha\u00eene commen\u00e7ant par la lettre P<\/li>\n<li>LIKE &#8220;*P&#8221; : cha\u00eene se terminant par la lettre P<\/li>\n<li>LIKE &#8220;*P*&#8221; : cha\u00eene comprenant la lettre P<\/li>\n<\/ul>\n<p>Exemple d&#8217;utilisation de l&#8217;op\u00e9rateur <em>LIKE<\/em> :<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">SELECT [_ECRITURES].CodeEntit\u00e9, [_ECRITURES].EcritureNum, [_ECRITURES].JournalCode, [_ECRITURES].JournalLib, [_ECRITURES].EcritureDateComptable, [_ECRITURES].CompteNumHarmonis\u00e9, [_ECRITURES].CompteNum, [_ECRITURES].CompteLib, [_ECRITURES].CompteAuxNum, [_ECRITURES].CompteAuxLib, [_ECRITURES].EcritureLib, [_ECRITURES].D\u00e9bit, [_ECRITURES].Cr\u00e9dit, [_ECRITURES].Solde, [_ECRITURES].Pi\u00e8ceR\u00e9f, [_ECRITURES].Pi\u00e8ceDate\r\nFROM _ECRITURES\r\nWHERE [_ECRITURES].TypeJournal=\"VENTES\" AND [_ECRITURES].Sch\u00e9mEcrit_Cpte6xSens Like \"*70[0-8]???xD+*\";\r\n<\/pre>\n<p style=\"text-align: justify;\">Dans cet exemple issu du <a href=\"https:\/\/www.auditsi.eu\/?page_id=8535\">programme d&#8217;analyse de donn\u00e9es comptables PADoCC<\/a>, l&#8217;op\u00e9rateur LIKE renverra <em>True<\/em> si le champ <em>Sch\u00e9mEcrit_Cpte6xSens<\/em> comprend (<em>*&#8230;*<\/em>) une cha\u00eene compos\u00e9e des caract\u00e8res 70 puis d&#8217;un chiffre de 0 \u00e0 8 (<em>[0-8]<\/em>) puis trois caract\u00e8res indiff\u00e9rents puis xD+. Cette comparaison permet de ne lister que les \u00e9critures comptables comprenant un compte de chiffre d&#8217;affaires commen\u00e7ant pas 700 \u00e0 708 qui est d\u00e9bit\u00e9 (en clair : annulation de chiffre d&#8217;affaires).<\/p>\n<p style=\"text-align: justify;\">Exemples de comparaisons reprises du <a href=\"https:\/\/support.microsoft.com\/fr-fr\/office\/op%C3%A9rateur-like-b2f7ef03-9085-4ffb-9829-eef18358e931\" target=\"_blank\" rel=\"noopener\">support technique Microsoft<\/a> :<\/p>\n<table id=\"tblID0EBBBAAA\" class=\"banded flipColors\">\n<thead>\n<tr>\n<th>\n<p class=\"\"><b class=\"ocpLegacyBold\"><br \/>\nGenre de correspondance<\/b><\/p>\n<\/th>\n<th><b class=\"ocpLegacyBold\"><br \/>\nMod\u00e8le<\/b><\/th>\n<th><b class=\"ocpLegacyBold\">Correspondance<\/b> <b class=\"ocpLegacyBold\"><br \/>\n(renvoie la valeur Vrai)<\/b><\/th>\n<th><b class=\"ocpLegacyBold\">Aucune correspondance<\/b> <b class=\"ocpLegacyBold\"><br \/>\n(renvoie la valeur Faux)<\/b><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Plusieurs caract\u00e8res<\/td>\n<td>a*a<\/td>\n<td>aa, aBa, aBBBa<\/td>\n<td>aBC<\/td>\n<\/tr>\n<tr>\n<td>&nbsp;<\/td>\n<td>*ab*<\/td>\n<td>abc, AABB, Xab<\/td>\n<td>aZb, bac<\/td>\n<\/tr>\n<tr>\n<td>Caract\u00e8re sp\u00e9cial<\/td>\n<td>a[*]a<\/td>\n<td>a*a<\/td>\n<td>aaa<\/td>\n<\/tr>\n<tr>\n<td>Plusieurs caract\u00e8res<\/td>\n<td>ab*<\/td>\n<td>abcdefg, abc<\/td>\n<td>cab, aab<\/td>\n<\/tr>\n<tr>\n<td>Caract\u00e8re isol\u00e9<\/td>\n<td>a?a<\/td>\n<td>aaa, a3a, aBa<\/td>\n<td>aBBBa<\/td>\n<\/tr>\n<tr>\n<td>Chiffre isol\u00e9<\/td>\n<td>a#a<\/td>\n<td>a0a, a1a, a2a<\/td>\n<td>aaa, a10a<\/td>\n<\/tr>\n<tr>\n<td>Plage de caract\u00e8res<\/td>\n<td>[a-z]<\/td>\n<td>f, p, j<\/td>\n<td>2, &amp;<\/td>\n<\/tr>\n<tr>\n<td>Hors d\u2019une plage<\/td>\n<td>[!a-z]<\/td>\n<td>9, &amp;, %<\/td>\n<td>b, a<\/td>\n<\/tr>\n<tr>\n<td>Pas un chiffre<\/td>\n<td>[!0-9]<\/td>\n<td>A, a, &amp;, ~<\/td>\n<td>0, 1, 9<\/td>\n<\/tr>\n<tr>\n<td>Combinaison<\/td>\n<td>a[!b-m]#<\/td>\n<td>An9, az0, a99<\/td>\n<td>\n<p class=\"\">abc, aj0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: justify;\">Approfondir le sujet : <a href=\"https:\/\/www.auditsi.eu\/?cat=1820\">Ma\u00eetriser le langage de requ\u00eate SQL<\/a>&nbsp;\/ <a href=\"https:\/\/www.auditsi.eu\/?p=6976\">les op\u00e9rateurs<\/a>&nbsp;\/ <a href=\"https:\/\/www.auditsi.eu\/?cat=50\">Analyse de donn\u00e9es<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"SQL%20%3A%20op%C3%A9rateur%20de%20comparaison%20LIKE\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Dans une clause WHERE, l&#8217;op\u00e9rateur LIKE permet d&#8217;effectuer une comparaison partielle entre le contenu d&#8217;un champ de donn\u00e9es et une cha\u00eene de caract\u00e8res. LIKE est un op\u00e9rateur de comparaison sp\u00e9cifique au langage de requ\u00eate SQL. Si la comparaison est r\u00e9alis\u00e9e, elle renvoie True sinon False. L&#8217;utilisation de l&#8217;op\u00e9rateur LIKE conjointement aux jokers (*, ?) permet &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=9624\" class=\"more-link\">Continue reading &lsquo;SQL : op\u00e9rateur de comparaison LIKE&rsquo; &raquo;<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"SQL%20%3A%20op%C3%A9rateur%20de%20comparaison%20LIKE\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[1820,282],"tags":[2497,2498,2025,1976,1142,57,2496],"class_list":["post-9624","post","type-post","status-publish","format-standard","hentry","category-requetes-sql","category-sql","tag-clause-where","tag-comparaison","tag-joker","tag-like","tag-operateurs-de-comparaison","tag-requete-sql","tag-wildcard"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/9624","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=9624"}],"version-history":[{"count":2,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/9624\/revisions"}],"predecessor-version":[{"id":9627,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/9624\/revisions\/9627"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=9624"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=9624"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=9624"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}