SQL Wildcards

Sql tutorial · PySpark.in

SQL Wildcards (%, _) 

Wildcards are special characters used with LIKE (and ILIKE in PostgreSQL) to do pattern matching in SQL.

• % → matches any number of characters (0, 1, or many)
• _ → matches exactly one character

They’re widely used in:

• search features (name/email/product search)
• cleaning/validating data formats
• finding codes, invoice numbers, ticket IDs, etc.

Sample Table: support_tickets

This table is perfect for learning wildcards because it contains different code formats (TCK-xxxx, BUG-xxxx, INC-xxxx) and different email domains/casing.

Create Table

```

sql

CREATE TABLE support_tickets (

ticket_id INTEGER PRIMARY KEY,

ticket_code TEXT NOT NULL,

customer_email TEXT NOT NULL,

status TEXT NOT NULL,

created_on TEXT NOT NULL -- store as ISO date string: YYYY-MM-DD (best for SQLite demos)

);

```

Insert Value in Table

```

sql

INSERT INTO support_tickets (ticket_id, ticket_code, customer_email, status, created_on) VALUES

(1, 'TCK-1001', 'rahul.singh@pyspark.in', 'OPEN', '2025-10-01'),

(2, 'TCK-1002', 'riya.sharma@gmail.com', 'CLOSED', '2025-10-02'),

(3, 'TCK-2001', 'amit.kumar@company.com', 'OPEN', '2025-10-05'),

(4, 'BUG-0007', 'neha.verma@yahoo.com', 'OPEN', '2025-10-06'),

(5, 'TCK-3009', 'kunal.mehta@GMAIL.com', 'CLOSED', '2025-10-10'),

(6, 'INC-0450', 'vishal.taneja@pyspark.in','OPEN', '2025-10-12');

```

Understand % and _ Step-by-Step

1) % Wildcard (Matches any number of characters)

1.1 Starts With (Prefix search)

Find ticket codes that start with TCK-

```

SELECT ticket_id, ticket_code, status

FROM support_tickets

WHERE ticket_code LIKE 'TCK-%';

```

Meaning:
TCK-% = starts with TCK- followed by anything.

1.2 Ends With (Suffix search)

Find emails that end with @pyspark.in

```

sql

SELECT ticket_id, customer_email

FROM support_tickets

WHERE customer_email LIKE '%@pyspark.in';

```

Meaning:
%@pyspark.in = anything before, ends with @pyspark.in.

1.3 Contains (Substring search)

Find all tickets where email contains gmail

```

sql

SELECT ticket_id, customer_email

FROM support_tickets

WHERE LOWER(customer_email) LIKE '%gmail%';

```

Why LOWER?
In SQLite, ILIKE is not supported. LOWER() makes matching case-insensitive.

2) _ Wildcard (Matches exactly one character)

2.1 Match fixed length pattern

Find ticket codes like BUG-0007 where it must be BUG- + 4 digits

```

sql

SELECT ticket_id, ticket_code

FROM support_tickets

WHERE ticket_code LIKE 'BUG-____';

```

Meaning:
BUG-____ = BUG- + exactly 4 characters.

2.2 Match a specific code format

Find ticket codes INC- followed by exactly 4 digits

```

sql

SELECT ticket_id, ticket_code

FROM support_tickets

WHERE ticket_code LIKE 'INC-____';

```

Note:

• Use % when length is unknown (any characters)
• Use _ when length is fixed (exactly one character)
• Combine them with LIKE to search patterns

Combine % and _ for Real Use Cases

1) Match “TCK-” codes with exactly 4 digits

Your data includes: TCK-1001, TCK-1002, TCK-2001, TCK-3009

```

sql

SELECT ticket_id, ticket_code

FROM support_tickets

WHERE ticket_code LIKE 'TCK-____';

```

Finds only codes that have exactly 4 characters after the hyphen.

2) Filter multiple patterns using OR

Show tickets that are either BUG or INC

```

sql

SELECT ticket_id, ticket_code, status

FROM support_tickets

WHERE ticket_code LIKE 'BUG-%'

OR ticket_code LIKE 'INC-%';

```

3) Exclude patterns using NOT LIKE

List all tickets that are NOT “TCK” type

```

sql

SELECT ticket_id, ticket_code

FROM support_tickets

WHERE ticket_code NOT LIKE 'TCK-%';

```

Practical Patterns, Escaping & Performance

1) Case-insensitive LIKE 

Use LOWER() or COLLATE NOCASE.

Option A: LOWER()

```

sql

SELECT ticket_id, customer_email

FROM support_tickets

WHERE LOWER(customer_email) LIKE '%gmail%';

```

Option B: COLLATE NOCASE

```

sql

SELECT ticket_id, customer_email

FROM support_tickets

WHERE customer_email LIKE '%gmail%' COLLATE NOCASE;

```

2) Escaping special characters (when searching literal % or _)

Sometimes your text actually contains % or _ and you want to search them literally.

Example: Search for _ as a normal character

```

sql

SELECT *

FROM some_table

WHERE some_column LIKE '%\_%' ESCAPE '\';

```

Explanation:

• \_ means underscore treated as a literal underscore (not wildcard)
• ESCAPE '\' tells SQL that backslash is the escape character

3) Performance tip (important in big tables)

• LIKE 'TCK-%' (prefix search) is usually faster and can use indexes.
• LIKE '%gmail%' (contains search) often forces a full scan (slower).

If performance matters:

• prefer prefix searches when possible
• consider search indexes / full-text search for large datasets

More Sql tutorials

• Introduction to Databases
• Introduction To DBMS
• Introduction To SQL
• Natural Join
• INNER JOIN
• LEFT JOIN

All tutorials · Try the free PySpark compiler · Practice challenges