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

Postgresql – SQL how to output a line with ONLY ONE "," and ONE character ";"

The query outputs lines with more than one comma, but I only need ONE comma and ONE semicolon

SELECT *
FROM table
WHERE history_coord is NULL
   OR history_coord LIKE '%,%' AND history_coord LIKE '%;%'

How do I change the request? maybe I don’t have to use "like"?

Tags:

3

Answers


  1. 0

    Maybe this will help you :

    SELECT *
FROM table
WHERE history_coord IS NULL
   OR history_coord REGEXP '^[^,]*,[^,]*;[^;]*$'

    In this query REGEXP pattern used to match one comma and one semicolon.

    • ^ is start of string
    • [^,] is matches with any number of characters that are not comma.
    • , is matches with comma.
    • ; is matches with semicolon.
    • [^;] is matches with any number if characters which are not semicolon.
    • $ is end of the string.
    Login or Signup to reply.
  2. 0

    Straightforward, just as you said – capture commas and semicolons with regular expressions using regexp_matches and then count them.

    SELECT *
FROM the_table
WHERE history_coord IS NULL
   OR (select count(*) from regexp_matches(history_coord , ',', 'g')) = 1
  and (select count(*) from regexp_matches(history_coord , ';', 'g')) = 1;

    P.S.
    If your PostgreSQL version is 15+ then use function regexp_count which is roughly equivalent to the scalar subqueries above.

    Login or Signup to reply.
  3. 0

    This is a way to do it by converting the string to an array using string_to_array and counts the length of the array using array_length and then substract 1 to count the number of occurrences of a substring within a string :

    Simple data :

    CREATE TABLE mytable (
  history_coord varchar(50)
);

insert into mytable values
('one semicolon ;'),
('one comma ,'),
('one semicolon ; and one comma ,'),
('two semicolon ;; and one comma ,'),
('TEXT');

    The query to get records with only one comma and one semi colon :

    SELECT *
FROM mytable
WHERE array_length(string_to_array(history_coord, ','), 1) - 1 = 1
      and array_length(string_to_array(history_coord, ';'), 1) - 1 = 1

    Result :

    history_coord
one semicolon ; and one comma ,

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search