How to concat strings so that trailing spaces remain
I have where clause where all fields are CHAR(10) type and may have trailing
spaces.
SELECT ...
FROM r1, r2
WHERE r1.c1 || r1.c2 || r1.c3 >= r2.c1 || r2.c2 || r2.c3;
This WHERE clause produces wrong result since || operator removes trailing
spaces.
Correct result is produced if || operator does not remove trailing spaces.
How to concat strings in this expression so that trailing spaces are
significiant or how to re-write this join condition so that trailing spaces
are used for comparison?
Andrus.
"Andrus" <eetasoft@online.ee> writes:
I have where clause where all fields are CHAR(10) type and may have trailing
spaces.
...
Correct result is produced if || operator does not remove trailing spaces.
If you think that trailing spaces are significant data, you should
probably be using VARCHAR not CHAR datatype.
regards, tom lane
If you think that trailing spaces are significant data, you should
probably be using VARCHAR not CHAR datatype.
I have existing database where there are only CHAR columns, no any VARCHAR
column.
I'm not sure will my appl work if I change all char columns to varchar
columns .
Is it reasonable to change all occurences of CHAR to VARCHAR in database ?
Are the following clauses exactly same for fields of type CHAR(10):
WHERE r1.c1::VARCHAR(10) || r1.c2::VARCHAR(10) || r1.c3::VARCHAR(10) >=
r2.c1::VARCHAR(10) || r2.c2::VARCHAR(10) ||
r2.c3::VARCHAR(10)
and
WHERE r1.c1>=r2.c1 and r1.c2>=r2.c2 and r1.c3>=r2.c3
and
WHERE (r1.c1, r1.c2,r1.c3) >= (r2.c1, r2.c2,r2.c3)
Andrus.