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

Postgresql problem with similar to and pattern behaviour

I came to a standtstill with a relatively trivial issue.
Such a simple query:

select distinct field_name,length(field_name) from table_a where
field_name similar to `2[0-9][0-9]%`;

returns the following rows:

field_name|length|
----------+------+
200       |     3|
203       |     3|
210       |     3|

But the very similar one, where only ‘%’ in the expression has been omitted returns no rows:

  select distinct field_name,length(field_name) from table_a where 
         field_name similar to  '[23][0-9][0-2]';

The field_name column is defined as CHAR(10).
The problem probably results from some characters at the end of the field but I don’t know how to eliminate them.
Would you mind telling me how to solve it?

Checked postgresql documentation and tutorials,
searched on google and stackoverflow wtihout success.

Tags:

2

Answers


  1. 0

    Just a quick guess: In the first query you are using the % Wildcard. So the char must not be exactly 3 digits long. Maybe there are some trailing spaces in your data?

    Try something like that:

    SELECT DISTINCT field_name, LENGTH(field_name)
FROM table_a
WHERE TRIM(field_name) SIMILAR TO '[23][0-9][0-2]'

    Let me know if that solves your issue.

    Login or Signup to reply.
  2. 0 
    CREATE TABLE table_a (
  field_name CHAR(10)
);

INSERT INTO table_a (field_name) VALUES 
('200       '), 
('203       '), 
('210       '), 
('230       '), 
('250       ');

INSERT INTO table_a (field_name) VALUES 
('200'), 
('203'), 
('210'), 
('230'), 
('250');



    CREATE TABLE

    INSERT 0 5

    INSERT 0 5

    SELECT DISTINCT field_name, LENGTH(field_name) 
FROM table_a 
WHERE field_name SIMILAR TO '[23][0-9][0-2]'
    field_name length 
    SELECT 0

    SELECT DISTINCT TRIM(field_name), LENGTH(TRIM(field_name)) 
FROM table_a 
WHERE TRIM(field_name) SIMILAR TO '[23][0-9][0-2]'
    btrim length
    200 3
    210 3
    230 3
    250 3 
    SELECT 4

    fiddle

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

Back To Top
Search