YouTip

The SQLite **GLOB** operator is used to match text values against a pattern specified with wildcards. If the search expression matches the pattern expression, the GLOB operator returns true (1). Unlike the LIKE operator, GLOB is case-sensitive and follows UNIX syntax for the following wildcards: * `*`: Matches zero, one, or multiple digits or characters. * `?`: Represents a single digit or character. * `[...]`: Matches any one of the characters specified within the square brackets. For example, `` matches any single character "a", "b", or "c". * `[^...]`: Matches any character not specified within the square brackets. For example, `[^abc]` matches any character that is not "a", "b", or "c". These symbols can be combined. The basic syntax for * and ? is as follows: SELECT FROM table_name WHERE column GLOB 'XXXX*'or SELECT FROM table_name WHERE column GLOB '*XXXX*'or SELECT FROM table_name WHERE column GLOB 'XXXX?'or SELECT FROM table_name WHERE column GLOB '?XXXX'or SELECT FROM table_name WHERE column GLOB '?XXXX?'or SELECT FROM table_name WHERE column GLOB '????' You can combine N number of conditions using the AND or OR operators. Here, XXXX can be any numeric or string value. The following examples demonstrate the differences in the GLOB clause with '*' and '?' operators: | Statement | Description | | --- | --- | | WHERE SALARY GLOB '200*' | Finds any value starting with 200 | | WHERE SALARY GLOB '*200*' | Finds any value containing 200 anywhere | | WHERE SALARY GLOB '?00*' | Finds any value where the second and third digits are 00 | | WHERE SALARY GLOB '2??' | Finds any value starting with 2 and having a length of 3 characters, for example, it may match values like "200", "2A1", "2B2", etc. | | WHERE SALARY GLOB '*2' | Finds any value ending with 2 | | WHERE SALARY GLOB '?2*3' | Finds any value where the second digit is 2 and ends with 3 | | WHERE SALARY GLOB '2???3' | Finds any value with a length of 5 digits, starting with 2 and ending with 3 | Let's take a practical example. Suppose the COMPANY table has the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0 Here is an example that displays all records from the COMPANY table where AGE starts with 2: sqlite> SELECT * FROM COMPANY WHERE AGE GLOB '2*'; This will produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------2 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0 Here is an example that displays all records from the COMPANY table where the ADDRESS text contains a hyphen (-): sqlite> SELECT * FROM COMPANY WHERE ADDRESS GLOB '*-*'; This will produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------4 Mark 25 Rich-Mond 65000.06 Kim 22 South-Hall 45000.0 ### [...] Wildcard The [...] expression is used to match any single character from the set of characters specified within the square brackets. Example 1: Match product names starting with "A" or "B". SELECT * FROM products WHERE product_name LIKE '%'; This will match product names starting with "A" or "B". Example 2: Match phone numbers starting with "1", "2", or "3". SELECT * FROM customers WHERE phone_number LIKE '%'; This will match phone numbers starting with "1", "2", or "3". ### [^...] Wildcard The [^...] expression is used to match any single character not in the set of characters specified within the square brackets. Example 1: Match product codes that do not start with "X" or "Y". SELECT * FROM products WHERE product_code LIKE '[^XY]%'; This will match product codes that do not start with "X" or "Y". Example 2: Match usernames that do not contain numeric characters. SELECT * FROM users WHERE username LIKE '[^0-9]%'; This will match usernames that do not start with a numeric character.