PostGresql : Regex to select rows that have only one forward slash in them

August 28, 2023

I have some data with rows with data like

'abcd/12313:100', 
'abcd/12123:1001/XYS/ABCDXY',
'abcd/12123:1001/XYS/ABCDXY/TEYE'

I am trying to pick the rows that have one forward slash only using

select distinct id from public.table_name where id ~* '[/{0,1}]';

select distinct id from public.table_name where id ~* '/';

But I keep getting both rows back while I need to get only the first one (i.e :’abcd/12313:100′)

I am running PostGresql 14.9.

Any help would be much appreciated.

Thanks

>Solution :

You don’t need a regular expression to match only those rows where exactly one forward slash appears in the id column:

SELECT DISTINCT id
FROM public.table_name
WHERE LENGTH(id) - LENGTH(REPLACE(id, '/', '')) = 1;

LENGTH(id) gives you the original length of the string in the id column, and LENGTH(REPLACE(id, ‘/’, ”)) gives you the length of the string after all forward slashes have been removed. Subtracting the latter from the former will give you the number of forward slashes in the string