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
$ not working?
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;
ID SYMBOL COST_IDS 2 FV02 107621,123421 3 FV03 111521; 107621
Your regular expression does not work as:
[^|s|,|;]matches a single character that is not either
[$|s|,|;]matches a single character that is either
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
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