CODEWITHSUNDEEP
sqloracle-databaseoracle10g
billabrian6
billabrian6

Reputation: 431

How to use charlist wildcard in Oracle SQL?

I'm trying to find the records where the first letter of the last name is between 's' and 'z'. This is how I was trying to do this:

WHERE UPPER(last_name) LIKE '[S-Z]%'

I know someone will ask, so yes there are records in the database that the last name starts with one of these letters.

Thanks in advance.

Upvotes: 1

Views: 3843

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

You can't use regular expression syntax with a normal like, but you can use regexp_like instead:

WHERE REGEXP_LIKE(UPPER(last_name), '^[S-Z]')

Or a slightly more brute-force approach:

WHERE UPPER(SUBSTR(last_name, 1, 1)) BETWEEN 'S' AND 'Z'

or even more explicitly:

WHERE UPPER(SUBSTR(last_name, 1, 1)) IN ('S','T','U','V','W','X','Y','Z')

Upvotes: 5

user937424
user937424

Reputation: 65

We can user the REGEXP function like this and it will improve the performance also -

select * from your_table where REGEXP_LIKE(upper(last_name),'^[S-Z](*)');

Upvotes: 2

Related Questions