|
Home > Archive > PostgreSQL Discussion > March 2006 > How to concat strings so that trailing spaces remain
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to concat strings so that trailing spaces remain
|
|
| Andrus 2006-03-30, 1:29 pm |
| 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.
| |
| Tom Lane 2006-03-30, 1:29 pm |
| "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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Andrus 2006-03-31, 7:33 am |
| > 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.
|
|
|
|
|