Oracle SQL: Using WHERE LIKE but for specific whole words / REGEXP_LIKE

I have a table of invoices with cost identifiers saved in a specific way, non always standard, like below:

ID | SYMBOL | COST_IDS
---+--------+-------------------
 1 | FV01   | '1076219, 1081419'
 2 | FV02   | '107621,123421'
 3 | FV03   | '111521; 107621'

I would like to find invoices for a specific cost identifier.

The structure of the cost is (4 or more digits)+(2 year digits)

In Test case: 107621, the desired output would be ID: 2 and 3.

SELECT * FROM INVOICES WHERE COST_IDS like '%107621%' Is a wrong approach.

I found that I need to use REGEXP_LIKE and I am struggling with it. I know I need to find the whole world exactly, but not necessarily on the beginning.

Can anyone help me?

Edit: this seems to work in most cases, but fails with the end of the string:

SELECT * 
FROM INVOICES 
WHERE REGEXP_LIKE(COST_IDS, '[^|s|,|;]107621[$|s|,|;]')

Also is there a way to mark ‘non-digit’ character instead of writing specifically s|,|;?

Why is $ not working?

Answer

You can use D to match a non-digit:

SELECT *
FROM   INVOICES
WHERE  REGEXP_LIKE(COST_IDS, '(^|D)107621(D|$)')

Which, for the sample data:

CREATE TABLE invoices (ID, SYMBOL, COST_IDS) AS
SELECT 1, 'FV01', '1076219, 1081419' FROM DUAL UNION ALL
SELECT 2, 'FV02', '107621,123421' FROM DUAL UNION ALL
SELECT 3, 'FV03', '111521; 107621' FROM DUAL;

Outputs:

ID	SYMBOL	COST_IDS
2	FV02	107621,123421
3	FV03	111521; 107621

db<>fiddle here

---

Your regular expression does not work as:

• [^|s|,|;] matches a single character that is not either | or or s or | or , or | or ;
• [$|s|,|;] matches a single character that is either $ or | or or s or | or , or | or ;

If you want to match either the start-of-the-string or a white space character or a comma or a semi-colon then you want the regular expression (^|s|[,;]).

Similarly, if you want to match end-of-the-string or a white space character or a comma or a semi-colon then you want the regular expression ($|s|[,;]).