{"id":31477,"date":"2023-01-21T19:44:49","date_gmt":"2023-01-21T14:14:49","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/"},"modified":"2023-01-21T19:44:49","modified_gmt":"2023-01-21T14:14:49","slug":"solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/","title":{"rendered":"[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed]"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-32112816\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"32112816\" data-parentid=\"32112131\" data-score=\"0\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>I think I&#8217;ve figured out what you are trying to do now.  For example, you are expecting the query to match <code>R2.1.2<\/code> which lies between <code>R1.1.2<\/code> and <code>R106.2.3<\/code> according to your id scheme.<\/p>\n<p>First of all, your SQL is syntactically invalid.  Assuming that these ids are represented by a <code>VARCHAR<\/code> or similar, you need to use string literals in the SQL; e.g.<\/p>\n<pre><code>    select * from table where ticket_id between 'R1.1.2' and 'R106.2.3'; \n<\/code><\/pre>\n<p>But that doesn&#8217;t solve the problem.  The reason is that the above is equivalent to<\/p>\n<pre><code>    select * from table where ticket_id &gt;= 'R1.1.2' \n                        and ticket_id &lt;= 'R106.2.3';\n<\/code><\/pre>\n<p>where <code>&gt;=<\/code> and <code>&lt;=<\/code> are regular SQL string comparisons.  And that is the problem.  Because according to regular SQL string comparison, <code>'R2.1.2'<\/code> is <em>larger than<\/em> <code>'R106.2.3'<\/code>!!.<\/p>\n<p>In fact, I don&#8217;t think there is an easy way to express the range check in SQL.  You might be able to do it with a stored procedure that breaks the ticket id string into parts, converts the parts to numbers and compares them.  That is messy and would cause the query optimizer indigestion.  MySQL will end up doing table scans, no matter what indexes you define.<\/p>\n<p>If you want query the ids as number triples \/ tuples, then you really ought to change the schema, replacing the <code>VARCHAR<\/code> field with three (or more) <code>INT<\/code> fields.  <\/p>\n<p>Alternatively, if you know that the fields are in specific ranges, you could represent the id component with leading zeros; e.g.  R0000001.01.02.  However, this breaks if your range assumptions are incorrect; e.g. when you have too many primary ticket numbers.<\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">1<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] I think I&#8217;ve figured out what you are trying to do now. For example, you are expecting the query to match R2.1.2 which lies between R1.1.2 and R106.2.3 according to your id scheme. First of all, your SQL is syntactically invalid. Assuming that these ids are represented by a VARCHAR or similar, you need &#8230; <a title=\"[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed]\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/\" aria-label=\"More on [Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed]\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[340,341],"class_list":["post-31477","post","type-post","status-publish","format-standard","hentry","category-solved","tag-mysql","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed] - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] I think I&#8217;ve figured out what you are trying to do now. For example, you are expecting the query to match R2.1.2 which lies between R1.1.2 and R106.2.3 according to your id scheme. First of all, your SQL is syntactically invalid. Assuming that these ids are represented by a VARCHAR or similar, you need ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2023-01-21T14:14:49+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed]\",\"datePublished\":\"2023-01-21T14:14:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\\\/\"},\"wordCount\":268,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"mysql\",\"sql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\\\/\",\"name\":\"[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2023-01-21T14:14:49+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed] - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed] - JassWeb","og_description":"[ad_1] I think I&#8217;ve figured out what you are trying to do now. For example, you are expecting the query to match R2.1.2 which lies between R1.1.2 and R106.2.3 according to your id scheme. First of all, your SQL is syntactically invalid. Assuming that these ids are represented by a VARCHAR or similar, you need ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/","og_site_name":"JassWeb","article_published_time":"2023-01-21T14:14:49+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed]","datePublished":"2023-01-21T14:14:49+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/"},"wordCount":268,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["mysql","sql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/","name":"[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2023-01-21T14:14:49+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-select-from-table-where-ticket_id-between-r1-1-2-and-r106-2-3-this-is-not-work-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed]"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/31477","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=31477"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/31477\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=31477"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=31477"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=31477"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}