github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/sem/tree/testdata/eval/like (about) 1 # LIKE and NOT LIKE 2 3 eval 4 'TEST' LIKE 'TEST' 5 ---- 6 true 7 8 eval 9 'TEST' LIKE 'test' 10 ---- 11 false 12 13 eval 14 'TEST' LIKE 'TESTER' 15 ---- 16 false 17 18 eval 19 'TEST' LIKE '' 20 ---- 21 false 22 23 24 eval 25 '' LIKE '' 26 ---- 27 true 28 29 # Regex special characters. 30 31 eval 32 '[' LIKE '[' 33 ---- 34 true 35 36 eval 37 '.' LIKE '.' 38 ---- 39 true 40 41 eval 42 '.A' LIKE '._' 43 ---- 44 true 45 46 eval 47 'AB' LIKE '._' 48 ---- 49 false 50 51 eval 52 '.*B' LIKE '.*B' 53 ---- 54 true 55 56 eval 57 'AB' LIKE '.*B' 58 ---- 59 false 60 61 # Escaped character cases. 62 63 eval 64 '[' LIKE '\[' 65 ---- 66 true 67 68 eval 69 '.' LIKE '\.' 70 ---- 71 true 72 73 eval 74 '\' LIKE '\\%' 75 ---- 76 true 77 78 eval 79 '\' LIKE '%\\' 80 ---- 81 true 82 83 eval 84 '\' LIKE '%\\%' 85 ---- 86 true 87 88 eval 89 '\%' LIKE '\\\%' 90 ---- 91 true 92 93 eval 94 '\.*' LIKE '\\.*' 95 ---- 96 true 97 98 eval 99 '\.*' LIKE '\\.\*' 100 ---- 101 true 102 103 eval 104 '\.*' LIKE '\\\.\*' 105 ---- 106 true 107 108 eval 109 '\\.' LIKE '\\.' 110 ---- 111 false 112 113 eval 114 '\\.' LIKE '\\\\.' 115 ---- 116 true 117 118 eval 119 '\\.' LIKE '\\\\\.' 120 ---- 121 true 122 123 eval 124 '\A' LIKE '\\A' 125 ---- 126 true 127 128 eval 129 'A' LIKE '\\A' 130 ---- 131 false 132 133 eval 134 '_' LIKE '\_' 135 ---- 136 true 137 138 eval 139 '\' LIKE '\\' 140 ---- 141 true 142 143 eval 144 'A\A' LIKE '_\\_' 145 ---- 146 true 147 148 eval 149 '__' LIKE '_\\_' 150 ---- 151 false 152 153 eval 154 '\_' LIKE '\\\_' 155 ---- 156 true 157 158 eval 159 '\\' LIKE '\\' 160 ---- 161 false 162 163 eval 164 '\\' LIKE '\\_' 165 ---- 166 true 167 168 eval 169 '\\' LIKE '_\\' 170 ---- 171 true 172 173 eval 174 'A\' LIKE '_\\' 175 ---- 176 true 177 178 eval 179 '%' LIKE '\%' 180 ---- 181 true 182 183 eval 184 'ABC' LIKE '\AB%' 185 ---- 186 true 187 188 eval 189 'ABC' LIKE '\AB_' 190 ---- 191 true 192 193 eval 194 'ABC' LIKE '%B\C' 195 ---- 196 true 197 198 eval 199 'ABC' LIKE '_B\C' 200 ---- 201 true 202 203 eval 204 'TEST' LIKE 'TE\ST' 205 ---- 206 true 207 208 eval 209 '_漢' LIKE '\__' 210 ---- 211 true 212 213 eval 214 '漢漢' LIKE '漢\漢' 215 ---- 216 true 217 218 eval 219 '_漢' LIKE '\_\漢' 220 ---- 221 true 222 223 # LIKE with ESCAPE clause 224 225 eval 226 like_escape('A', '\A', '') 227 ---- 228 false 229 230 eval 231 like_escape('\', '\', '-') 232 ---- 233 true 234 235 eval 236 like_escape('\', '-\', '-') 237 ---- 238 true 239 240 eval 241 like_escape('\abc\', '-\___-\', '-') 242 ---- 243 true 244 245 eval 246 like_escape('\abc\', '\___\', '-') 247 ---- 248 true 249 250 eval 251 like_escape('\abc\', '-\___-\', '') 252 ---- 253 false 254 255 eval 256 like_escape('___', '\___', '') 257 ---- 258 false 259 260 eval 261 like_escape('aa', '__', '_') 262 ---- 263 false 264 265 eval 266 like_escape('aa', '_a_a', '_') 267 ---- 268 true 269 270 eval 271 like_escape('_', '__', '_') 272 ---- 273 true 274 275 eval 276 like_escape('__', '____', '_') 277 ---- 278 true 279 280 eval 281 like_escape('___', '______', '_') 282 ---- 283 true 284 285 eval 286 like_escape('abc', 'a%%', '%') 287 ---- 288 false 289 290 eval 291 like_escape('ab', '____', '_') 292 ---- 293 false 294 295 eval 296 like_escape('\a\b\c', '\_\_\_', '') 297 ---- 298 true 299 300 eval 301 like_escape('%%', '_%_%', '_') 302 ---- 303 true 304 305 eval 306 like_escape('%%a', '_%_%', '_') 307 ---- 308 false 309 310 eval 311 like_escape('\-\', '-\---\', '-') 312 ---- 313 true 314 315 eval 316 like_escape('\-\', '\--\', '-') 317 ---- 318 true 319 320 eval 321 like_escape('_%', 'a_a%', 'a') 322 ---- 323 true 324 325 eval 326 like_escape('\---\', '-\-------\', '-') 327 ---- 328 true 329 330 eval 331 like_escape('abc', '%bc', '%') 332 ---- 333 false 334 335 eval 336 like_escape('abc', '_bc', '_') 337 ---- 338 false 339 340 eval 341 like_escape('abc', '_b%', '_') 342 ---- 343 false 344 345 eval 346 like_escape('abc', '%b_', '%') 347 ---- 348 false 349 350 eval 351 like_escape('abc', '_a%', '_') 352 ---- 353 true 354 355 eval 356 like_escape('abc', '%a__', '%') 357 ---- 358 true 359 360 eval 361 like_escape('ww', '@w@w', '@') 362 ---- 363 true 364 365 eval 366 like_escape('\\', '@\@\', '@') 367 ---- 368 true 369 370 eval 371 like_escape('@ww', '@@w@w', '@') 372 ---- 373 true 374 375 eval 376 like_escape('@\', '@@@\', '@') 377 ---- 378 true 379 380 eval 381 like_escape('\', '@\', '@') 382 ---- 383 true 384 385 eval 386 like_escape('\@\', '@\@@@\', '@') 387 ---- 388 true 389 390 eval 391 like_escape('a', '日a', '日') 392 ---- 393 true 394 395 eval 396 like_escape('a日a', '%日日_', '日') 397 ---- 398 true 399 400 eval 401 like_escape('_漢', '漢_漢漢', '漢') 402 ---- 403 true 404 405 eval 406 like_escape('漢日', '漢漢漢日', '漢') 407 ---- 408 true 409 410 eval 411 like_escape('漢日', '漢%漢日', '漢') 412 ---- 413 false 414 415 eval 416 like_escape('%日_', '漢%漢日漢_', '漢') 417 ---- 418 true 419 420 eval 421 like_escape('a', '꧕', '�') 422 ---- 423 false 424 425 eval 426 like_escape('\꧕%', '�\꧕�%', '�') 427 ---- 428 true 429 430 # ILIKE with ESCAPE clause 431 432 eval 433 ilike_escape('A', '\a', '') 434 ---- 435 false 436 437 eval 438 ilike_escape('\', '\', '-') 439 ---- 440 true 441 442 eval 443 ilike_escape('\', '-\', '-') 444 ---- 445 true 446 447 eval 448 ilike_escape('\ABC\', '-\___-\', '-') 449 ---- 450 true 451 452 eval 453 ilike_escape('\ABC\', '\___\', '-') 454 ---- 455 true 456 457 eval 458 ilike_escape('\ABC\', '-\___-\', '') 459 ---- 460 false 461 462 eval 463 ilike_escape('___', '\___', '') 464 ---- 465 false 466 467 eval 468 ilike_escape('AA', '__', '_') 469 ---- 470 false 471 472 eval 473 ilike_escape('AA', '_a_a', '_') 474 ---- 475 true 476 477 eval 478 ilike_escape('_', '__', '_') 479 ---- 480 true 481 482 eval 483 ilike_escape('__', '____', '_') 484 ---- 485 true 486 487 eval 488 ilike_escape('___', '______', '_') 489 ---- 490 true 491 492 eval 493 ilike_escape('ABC', 'a%%', '%') 494 ---- 495 false 496 497 eval 498 ilike_escape('AB', '____', '_') 499 ---- 500 false 501 502 eval 503 ilike_escape('\A\B\C', '\_\_\_', '') 504 ---- 505 true 506 507 eval 508 ilike_escape('%%', '_%_%', '_') 509 ---- 510 true 511 512 eval 513 ilike_escape('%%a', '_%_%', '_') 514 ---- 515 false 516 517 eval 518 ilike_escape('\-\', '-\---\', '-') 519 ---- 520 true 521 522 eval 523 ilike_escape('\-\', '\--\', '-') 524 ---- 525 true 526 527 eval 528 ilike_escape('_%', 'a_a%', 'a') 529 ---- 530 true 531 532 eval 533 ilike_escape('\---\', '-\-------\', '-') 534 ---- 535 true 536 537 eval 538 ilike_escape('abc', '%bc', '%') 539 ---- 540 false 541 542 eval 543 ilike_escape('abc', '_bc', '_') 544 ---- 545 false 546 547 eval 548 ilike_escape('abc', '_b%', '_') 549 ---- 550 false 551 552 eval 553 ilike_escape('abc', '%b_', '%') 554 ---- 555 false 556 557 eval 558 ilike_escape('abc', '_a%', '_') 559 ---- 560 true 561 562 eval 563 ilike_escape('abc', '%a__', '%') 564 ---- 565 true 566 567 eval 568 ilike_escape('ww', '@w@w', '@') 569 ---- 570 true 571 572 eval 573 ilike_escape('\\', '@\@\', '@') 574 ---- 575 true 576 577 eval 578 ilike_escape('@ww', '@@w@w', '@') 579 ---- 580 true 581 582 eval 583 ilike_escape('@\', '@@@\', '@') 584 ---- 585 true 586 587 eval 588 ilike_escape('\', '@\', '@') 589 ---- 590 true 591 592 eval 593 ilike_escape('\@\', '@\@@@\', '@') 594 ---- 595 true 596 597 eval 598 ilike_escape('a', '日a', '日') 599 ---- 600 true 601 602 eval 603 ilike_escape('a日a', '%日日_', '日') 604 ---- 605 true 606 607 eval 608 ilike_escape('_漢', '漢_漢漢', '漢') 609 ---- 610 true 611 612 eval 613 ilike_escape('漢日', '漢漢漢日', '漢') 614 ---- 615 true 616 617 eval 618 ilike_escape('漢日', '漢%漢日', '漢') 619 ---- 620 false 621 622 eval 623 ilike_escape('%日_', '漢%漢日漢_', '漢') 624 ---- 625 true 626 627 eval 628 ilike_escape('abCD', 'AB-c-d', '-') 629 ---- 630 true 631 632 # NOT LIKE with ESCAPE clause 633 634 eval 635 not_like_escape('A', '\A', '') 636 ---- 637 true 638 639 eval 640 not_like_escape('\', '\', '-') 641 ---- 642 false 643 644 eval 645 not_like_escape('\', '-\', '-') 646 ---- 647 false 648 649 eval 650 not_like_escape('\abc\', '-\___-\', '-') 651 ---- 652 false 653 654 eval 655 not_like_escape('\abc\', '\___\', '-') 656 ---- 657 false 658 659 eval 660 not_like_escape('\abc\', '-\___-\', '') 661 ---- 662 true 663 664 eval 665 not_like_escape('___', '\___', '') 666 ---- 667 true 668 669 eval 670 not_like_escape('aa', '__', '_') 671 ---- 672 true 673 674 eval 675 not_like_escape('aa', '_a_a', '_') 676 ---- 677 false 678 679 eval 680 not_like_escape('_', '__', '_') 681 ---- 682 false 683 684 eval 685 not_like_escape('__', '____', '_') 686 ---- 687 false 688 689 eval 690 not_like_escape('___', '______', '_') 691 ---- 692 false 693 694 eval 695 not_like_escape('abc', 'a%%', '%') 696 ---- 697 true 698 699 eval 700 not_like_escape('ab', '____', '_') 701 ---- 702 true 703 704 eval 705 not_like_escape('\a\b\c', '\_\_\_', '') 706 ---- 707 false 708 709 eval 710 not_like_escape('%%', '_%_%', '_') 711 ---- 712 false 713 714 eval 715 not_like_escape('%%a', '_%_%', '_') 716 ---- 717 true 718 719 eval 720 not_like_escape('\-\', '-\---\', '-') 721 ---- 722 false 723 724 eval 725 not_like_escape('\-\', '\--\', '-') 726 ---- 727 false 728 729 eval 730 not_like_escape('_%', 'a_a%', 'a') 731 ---- 732 false 733 734 eval 735 not_like_escape('\---\', '-\-------\', '-') 736 ---- 737 false 738 739 eval 740 not_like_escape('abc', '%bc', '%') 741 ---- 742 true 743 744 eval 745 not_like_escape('abc', '_bc', '_') 746 ---- 747 true 748 749 eval 750 not_like_escape('abc', '_b%', '_') 751 ---- 752 true 753 754 eval 755 not_like_escape('abc', '%b_', '%') 756 ---- 757 true 758 759 eval 760 not_like_escape('abc', '_a%', '_') 761 ---- 762 false 763 764 eval 765 not_like_escape('abc', '%a__', '%') 766 ---- 767 false 768 769 eval 770 not_like_escape('ww', '@w@w', '@') 771 ---- 772 false 773 774 eval 775 not_like_escape('\\', '@\@\', '@') 776 ---- 777 false 778 779 eval 780 not_like_escape('@ww', '@@w@w', '@') 781 ---- 782 false 783 784 eval 785 not_like_escape('@\', '@@@\', '@') 786 ---- 787 false 788 789 eval 790 not_like_escape('\', '@\', '@') 791 ---- 792 false 793 794 eval 795 not_like_escape('\@\', '@\@@@\', '@') 796 ---- 797 false 798 799 eval 800 not_like_escape('a', '日a', '日') 801 ---- 802 false 803 804 eval 805 not_like_escape('a日a', '%日日_', '日') 806 ---- 807 false 808 809 eval 810 not_like_escape('_漢', '漢_漢漢', '漢') 811 ---- 812 false 813 814 eval 815 not_like_escape('漢日', '漢漢漢日', '漢') 816 ---- 817 false 818 819 eval 820 not_like_escape('漢日', '漢%漢日', '漢') 821 ---- 822 true 823 824 eval 825 not_like_escape('%日_', '漢%漢日漢_', '漢') 826 ---- 827 false 828 829 # NOT ILIKE with ESCAPE clause 830 831 eval 832 not_ilike_escape('A', '\a', '') 833 ---- 834 true 835 836 eval 837 not_ilike_escape('\', '\', '-') 838 ---- 839 false 840 841 eval 842 not_ilike_escape('\', '-\', '-') 843 ---- 844 false 845 846 eval 847 not_ilike_escape('\ABC\', '-\___-\', '-') 848 ---- 849 false 850 851 eval 852 not_ilike_escape('\ABC\', '\___\', '-') 853 ---- 854 false 855 856 eval 857 not_ilike_escape('\ABC\', '-\___-\', '') 858 ---- 859 true 860 861 eval 862 not_ilike_escape('___', '\___', '') 863 ---- 864 true 865 866 eval 867 not_ilike_escape('AA', '__', '_') 868 ---- 869 true 870 871 eval 872 not_ilike_escape('AA', '_a_a', '_') 873 ---- 874 false 875 876 eval 877 not_ilike_escape('_', '__', '_') 878 ---- 879 false 880 881 eval 882 not_ilike_escape('__', '____', '_') 883 ---- 884 false 885 886 eval 887 not_ilike_escape('___', '______', '_') 888 ---- 889 false 890 891 eval 892 not_ilike_escape('ABC', 'a%%', '%') 893 ---- 894 true 895 896 eval 897 not_ilike_escape('AB', '____', '_') 898 ---- 899 true 900 901 eval 902 not_ilike_escape('\A\B\C', '\_\_\_', '') 903 ---- 904 false 905 906 eval 907 not_ilike_escape('%%', '_%_%', '_') 908 ---- 909 false 910 911 eval 912 not_ilike_escape('%%a', '_%_%', '_') 913 ---- 914 true 915 916 eval 917 not_ilike_escape('\-\', '-\---\', '-') 918 ---- 919 false 920 921 eval 922 not_ilike_escape('\-\', '\--\', '-') 923 ---- 924 false 925 926 eval 927 not_ilike_escape('_%', 'a_a%', 'a') 928 ---- 929 false 930 931 eval 932 not_ilike_escape('\---\', '-\-------\', '-') 933 ---- 934 false 935 936 eval 937 not_ilike_escape('abc', '%bc', '%') 938 ---- 939 true 940 941 eval 942 not_ilike_escape('abc', '_bc', '_') 943 ---- 944 true 945 946 eval 947 not_ilike_escape('abc', '_b%', '_') 948 ---- 949 true 950 951 eval 952 not_ilike_escape('abc', '%b_', '%') 953 ---- 954 true 955 956 eval 957 not_ilike_escape('abc', '_a%', '_') 958 ---- 959 false 960 961 eval 962 not_ilike_escape('abc', '%a__', '%') 963 ---- 964 false 965 966 eval 967 not_ilike_escape('ww', '@w@w', '@') 968 ---- 969 false 970 971 eval 972 not_ilike_escape('\\', '@\@\', '@') 973 ---- 974 false 975 976 eval 977 not_ilike_escape('@ww', '@@w@w', '@') 978 ---- 979 false 980 981 eval 982 not_ilike_escape('@\', '@@@\', '@') 983 ---- 984 false 985 986 eval 987 not_ilike_escape('\', '@\', '@') 988 ---- 989 false 990 991 eval 992 not_ilike_escape('\@\', '@\@@@\', '@') 993 ---- 994 false 995 996 eval 997 not_ilike_escape('a', '日a', '日') 998 ---- 999 false 1000 1001 eval 1002 not_ilike_escape('a日a', '%日日_', '日') 1003 ---- 1004 false 1005 1006 eval 1007 not_ilike_escape('_漢', '漢_漢漢', '漢') 1008 ---- 1009 false 1010 1011 eval 1012 not_ilike_escape('漢日', '漢漢漢日', '漢') 1013 ---- 1014 false 1015 1016 eval 1017 not_ilike_escape('漢日', '漢%漢日', '漢') 1018 ---- 1019 true 1020 1021 eval 1022 not_ilike_escape('%日_', '漢%漢日漢_', '漢') 1023 ---- 1024 false 1025 1026 eval 1027 not_ilike_escape('abCD', 'AB-c-d', '-') 1028 ---- 1029 false 1030 1031 # optimizedLikeFunc expressions. 1032 1033 eval 1034 'TEST' LIKE 'TE%' 1035 ---- 1036 true 1037 1038 eval 1039 'TEST' LIKE '%E%' 1040 ---- 1041 true 1042 1043 eval 1044 'TEST' LIKE '%e%' 1045 ---- 1046 false 1047 1048 eval 1049 'TEST' LIKE 'TES_' 1050 ---- 1051 true 1052 1053 eval 1054 'TEST' LIKE 'TE_%' 1055 ---- 1056 true 1057 1058 eval 1059 'TEST' LIKE 'TE_' 1060 ---- 1061 false 1062 1063 eval 1064 'TEST' LIKE '%' 1065 ---- 1066 true 1067 1068 eval 1069 'TEST' LIKE '%R' 1070 ---- 1071 false 1072 1073 eval 1074 'T' LIKE '\_' 1075 ---- 1076 false 1077 1078 eval 1079 'T' LIKE '\%' 1080 ---- 1081 false 1082 1083 eval 1084 'TE_T' LIKE 'TE\_T' 1085 ---- 1086 true 1087 1088 eval 1089 'TE\AT' LIKE 'TE\_T' 1090 ---- 1091 false 1092 1093 eval 1094 'TES%T' LIKE 'TES\%T' 1095 ---- 1096 true 1097 1098 eval 1099 'TES\AT' LIKE 'TES\%T' 1100 ---- 1101 false 1102 1103 eval 1104 'T' LIKE '_' 1105 ---- 1106 true 1107 1108 eval 1109 'TE' LIKE '_' 1110 ---- 1111 false 1112 1113 eval 1114 'TE' LIKE '_%' 1115 ---- 1116 true 1117 1118 eval 1119 'T' LIKE '_%' 1120 ---- 1121 true 1122 1123 eval 1124 '' LIKE '_%' 1125 ---- 1126 false 1127 1128 eval 1129 'TE' LIKE '%_' 1130 ---- 1131 true 1132 1133 eval 1134 '' LIKE '%_' 1135 ---- 1136 false 1137 1138 eval 1139 'T' LIKE '%_' 1140 ---- 1141 true 1142 1143 eval 1144 'TEST' LIKE '_ES_' 1145 ---- 1146 true 1147 1148 eval 1149 '' LIKE '__' 1150 ---- 1151 false 1152 1153 eval 1154 'A' LIKE 'T_' 1155 ---- 1156 false 1157 1158 eval 1159 'A' LIKE '_T' 1160 ---- 1161 false 1162 1163 eval 1164 'TEST' LIKE '_E%' 1165 ---- 1166 true 1167 1168 eval 1169 'TEST' LIKE '_E\%' 1170 ---- 1171 false 1172 1173 eval 1174 'TES_' LIKE '%S\_' 1175 ---- 1176 true 1177 1178 eval 1179 'TES%' LIKE '%S\%' 1180 ---- 1181 true 1182 1183 eval 1184 'TES_' LIKE '_ES\_' 1185 ---- 1186 true 1187 1188 eval 1189 'TES%' LIKE '_ES\%' 1190 ---- 1191 true 1192 1193 eval 1194 'TEST' LIKE '%S_' 1195 ---- 1196 true 1197 1198 eval 1199 'TEST' LIKE '%S\_' 1200 ---- 1201 false 1202 1203 eval 1204 'TEST' NOT LIKE '%E%' 1205 ---- 1206 false 1207 1208 eval 1209 'TEST' NOT LIKE 'TES_' 1210 ---- 1211 false 1212 1213 eval 1214 'TEST' NOT LIKE 'TeS_' 1215 ---- 1216 true 1217 1218 eval 1219 'TEST' NOT LIKE 'TE_' 1220 ---- 1221 true 1222 1223 # ILIKE and NOT ILIKE 1224 1225 eval 1226 'TEST' ILIKE 'TEST' 1227 ---- 1228 true 1229 1230 eval 1231 'TEST' ILIKE 'test' 1232 ---- 1233 true 1234 1235 eval 1236 'TEST' ILIKE 'TE%' 1237 ---- 1238 true 1239 1240 eval 1241 'TEST' ILIKE '%E%' 1242 ---- 1243 true 1244 1245 eval 1246 'TEST' ILIKE '%e%' 1247 ---- 1248 true 1249 1250 eval 1251 'TEST' ILIKE 'TES_' 1252 ---- 1253 true 1254 1255 eval 1256 'TEST' ILIKE 'TE_%' 1257 ---- 1258 true 1259 1260 eval 1261 'TEST' ILIKE 'TE_' 1262 ---- 1263 false 1264 1265 eval 1266 'TEST' ILIKE '%' 1267 ---- 1268 true 1269 1270 eval 1271 'TEST' ILIKE '%R' 1272 ---- 1273 false 1274 1275 eval 1276 'TEST' ILIKE 'TESTER' 1277 ---- 1278 false 1279 1280 eval 1281 'TEST' ILIKE 'tester' 1282 ---- 1283 false 1284 1285 eval 1286 'TEST' ILIKE '' 1287 ---- 1288 false 1289 1290 eval 1291 '' ILIKE '' 1292 ---- 1293 true 1294 1295 eval 1296 'T' ILIKE '_' 1297 ---- 1298 true 1299 1300 eval 1301 'TE' ILIKE '_' 1302 ---- 1303 false 1304 1305 eval 1306 'TEST' NOT ILIKE '%E%' 1307 ---- 1308 false 1309 1310 eval 1311 'TEST' NOT ILIKE 'TES_' 1312 ---- 1313 false 1314 1315 eval 1316 'TEST' NOT ILIKE 'TeS_' 1317 ---- 1318 false 1319 1320 eval 1321 'TEST' NOT ILIKE 'TE_' 1322 ---- 1323 true