Searching for a Value that can have Variations - SQL

Question

I was given the task of listing the companies and department names located in either Victoria or Tasmania. The value of 'Victoria' could have variations like 'VIC', 'vic', etc. (Same goes for Tasmania.)

Though this might seem like a simple task I couldn't figure out what to do. I tried using the percent sign (%) in front of some characters but it obviously didn't work.

How do I search for all the companies that are in Victoria and Tasmania with all the possible variations? Thanks!

EDIT: I'm using Oracle SQL Developer for the task.

score 0 · Accepted Answer · answered Sep 29 '19 at 08:00

0

Maybe you can use regexp queries. Example:

SELECT 
    departament
FROM
    location
WHERE
    departmantname REGEXP '^(VIC|vic|Vic)'
ORDER BY id;

MYSQL: http://www.mysqltutorial.org/mysql-regular-expression-regexp.aspx

MSSQL: MSSQL Regular expression

answered Sep 29 '19 at 08:00

MihaiM

179
6

Hi @MihaiM, I'm using Oracle SQL Developer for the task; do you have another solution for Oracle? Thank you so much for your help! – Sep 29 '19 at 08:04
https://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_re.htm#1007582 It seems that oracle has more powerfull methods for regex queries. Maybe you can define a regex pattern for every string that starts with V or v and ends with A or a – MihaiM Sep 29 '19 at 08:06

Barbaros Özhan · Answer 2 · 2019-09-29T11:09:15.670

0

You can use regexp_like() condition to filter with case-insensitive mode ('i'):

select location
  from dept
 where regexp_like(location,'ViCtoria|Tasmania','i')

where pipe(s) stands for listing other filtering options conforming to OR logic.

Demo

edited Sep 29 '19 at 11:09

answered Sep 29 '19 at 10:17

Barbaros Özhan

59,113
10
31
55

score 0 · Answer 3 · answered Sep 29 '19 at 11:50

From what you describe, like should be sufficient:

where lower(location) like 'vic%' or
      lower(location) like 'tas%'

You haven't specified what all the variations are. This simply returns all locations that start with one of these prefixes.

You can express this as a regular expression as well:

where regexp_like(location, '^(vic,tas)', 'i')

With the 'i' match parameter, the comparison is case-insensitive.

Searching for a Value that can have Variations - SQL

3 Answers3