Oracle正则表达式匹配特定11位格式字符串的技术问询

Let's build the exact regex you need, step by step, based on your requirements:

Breakdown of Requirements & Corresponding Regex Parts #

First, let's map each position's rule to regex components (Oracle uses POSIX-style regex with handy set operations):

• Total length 11 characters: We'll use ^ (start of string) and $ (end of string) to enforce a full-string match—critical because Oracle's REGEXP_LIKE defaults to partial matching otherwise.
• Positions 1,4,7,10,11: Digits 0-9 → Use [0-9] (you can also use \d as an equivalent shorthand in Oracle).
• Positions 2,5,8,9: Uppercase letters (A-Z) excluding S, L, O, I, B, Z → Use Oracle's set difference syntax: [A-Z&&[^SLIBZO]]—this targets any A-Z letter that isn't in your excluded list.
• Positions 3,6: Letters or digits → Use [A-Z0-9] (adjust to [A-Za-z0-9] if you need to support lowercase letters here too).

Full Regular Expression #

Here's the complete regex string tailored to your rules:

^[0-9][A-Z&&[^SLIBZO]][A-Z0-9][0-9][A-Z&&[^SLIBZO]][A-Z0-9][0-9][A-Z&&[^SLIBZO]][A-Z&&[^SLIBZO]][0-9][0-9]$

Usage Example in Oracle SQL #

To apply this regex in a query (e.g., filtering a column for valid strings):

SELECT your_column_name
FROM your_table
WHERE REGEXP_LIKE(your_column_name, '^[0-9][A-Z&&[^SLIBZO]][A-Z0-9][0-9][A-Z&&[^SLIBZO]][A-Z0-9][0-9][A-Z&&[^SLIBZO]][A-Z&&[^SLIBZO]][0-9][0-9]$', 'c');

• The 'c' flag makes the match case-sensitive. If you want to allow lowercase letters in positions 2,5,8,9 or 3,6, remove the 'c' flag or replace it with 'i' (case-insensitive mode).

Quick Validation Tips #

Test with sample strings to confirm it works as expected:

• Valid example: 1K23X45PQ67 (each position adheres to your rules)
• Invalid example: 1S23B45CD67 (position 2 uses excluded letter S)

内容的提问来源于stack exchange，提问作者Mubeena Elizabeth Majeed