Oracle SQL Developer：如何搜索含变体地区值以筛选企业及部门？

Got it, let's sort this out for you. The reason your initial % wildcard query wasn't working is likely because you weren't accounting for case differences and the various abbreviations properly. Here are three solid approaches to find companies/departments in Victoria (including VIC/vic etc.) and Tasmania (and its variants) in Oracle SQL Developer:

Convert your region column to a consistent case (all upper or all lower) and match against the standardized versions of your target regions. This eliminates case sensitivity issues entirely:

SELECT company_name, department_name
FROM your_table_name
WHERE UPPER(region_column) IN ('VICTORIA', 'VIC', 'TASMANIA', 'TAS');

Just replace your_table_name with your actual table name, and region_column with the column storing the location data. This will catch any variation like vic, Victoria, TAS, or tasmania because we're forcing everything to uppercase before comparing.

If your region column might include extra context (like "Melbourne, VIC" or "Hobart, Tasmania"), use LIKE with wildcards alongside case conversion to capture those:

SELECT company_name, department_name
FROM your_table_name
WHERE UPPER(region_column) LIKE '%VICTORIA%'
   OR UPPER(region_column) LIKE '%VIC%'
   OR UPPER(region_column) LIKE '%TASMANIA%'
   OR UPPER(region_column) LIKE '%TAS%';

The % wildcard matches any sequence of characters (including none), so this will find rows where the region value contains any of your target terms, regardless of surrounding text.

Oracle's REGEXP_LIKE function lets you use regular expressions to handle all variants in one go, with built-in case insensitivity:

SELECT company_name, department_name
FROM your_table_name
WHERE REGEXP_LIKE(region_column, 'victoria|vic|tasmania|tas', 'i');

The 'i' flag makes the match case-insensitive, so it'll pick up Vic, TASMANIA, vic, etc. If you need to match only rows where the region column is exactly one of these terms (not just containing it), add start/end anchors to the regex:

SELECT company_name, department_name
FROM your_table_name
WHERE REGEXP_LIKE(region_column, '^(victoria|vic|tasmania|tas)$', 'i');

The ^ marks the start of the string, and $ marks the end, ensuring you don't accidentally match longer strings that include your target terms.

Quick Notes: #

• Don't forget to swap out placeholder names (your_table_name, region_column) with your actual database objects.
• If there are other region variants you need to include (like TASM for Tasmania), just add them to the IN list or the regex pattern.

内容的提问来源于stack exchange，提问作者user9832881