Question 239328 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    If you want to use a regular expression, you can match against:

    ^[^/]*/[^/]*$

    which matches strings which have

    • ^ : beginning of string
    • [^/]* : some number of non-/ characters
    • / : a /
    • [^/]* : some number of non-/ characters
    • $ : end of string

    In SQL:

    SELECT *
FROM table_name
WHERE id ~ '^[^/]*/[^/]*$'

    Demo on dbfiddle

    However note using simple string operations such as the answer by @Manuvo would almost certainly be faster.

    Login or Signup to reply.
  3. 0

    This is how you would say it, the column contains one slash, but not more.

    select * from t
where id like '%/%' and 
id not like '%/%/%'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search