SELECT (regexp_matches(gfrgebaeude_details_value, '[0-9]{5,}'))[1]::int AS value FROM gfrgebaeude_details WHERE gfrgebaeude_details_value ~ '[0-9]{5,}';
Damit werden aus einer Textspalte alle Integer-Werte ausgelesen. Diese Werte sollen in eine neue Spalte (Integer) eingetragen werden.
Wie wird aber gewährleistet, dass die gecasten Werte an der richtigen Position (zu den Text-Werten) eingetragen werden?
test=# select * from t1;
t
-------
123
567
abc89
(3 Zeilen)
Zeit: 0,231 ms
test=*# alter table t1 add column x int;
ALTER TABLE
Zeit: 0,356 ms
test=*# update t1 set x = regexp_replace(t,'[^0-9]','','g')::int;
UPDATE 3
Zeit: 0,360 ms
test=*# select * from t1;
t | x
-------+-----
123 | 123
567 | 567
abc89 | 89
(3 Zeilen)
geodata=> SELECT gfrgebaeude_details.gfrgebaeude_details_value FROM gfrgebaeude_details WHERE gfrgebaeude_details_key = 'sp1' LIMIT 1;
gfrgebaeude_details_value
---------------------------
40006423
(1 Zeile)
geodata=> SELECT kontaktnummer, name FROM gfo_partner LIMIT 1;
kontaktnummer | name
---------------+------------------------------------
40030231 | Eintrag
(1 Zeile)
Nun joine ich die kontaktnummer (Integer) mit gfrgebaeude_details_value (Textfeld)
Im Textfeld sind Datums-, Text- und Integereinträge gemischt.
select gfo_partner.name, gfrgebaeude_details.gfrgebaeude_details_value from gfo_partner left join gfrgebaeude_details on (gfo_partner.kontaktnummer = regexp_replace(gfrgebaeude_details.gfrgebaeude_details_value,'[^0-9]','','g')::int);
ERROR: invalid input syntax for integer: ""
Nein. Du hast da offensichtlich einen String, der nur aus Leerzeichen besteht.
test=*# copy t1 from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 4
>> \.
test=*# commit;
COMMIT
test=# select t1.*, t2.* from t1 left join t2 on (t1.id=regexp_replace(public.t2.col1,'[^0-9]','','g')::int);
id | col1 | name
----+---------+-------
1 | 1 | name1
2 | 2 | name2
3 | abc3xyz | name3
4 | |
(4 rows)
test=*# copy t2 from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> name4
>> \.
test=*# commit;
COMMIT
test=# select t1.*, t2.* from t1 left join t2 on (t1.id=regexp_replace(public.t2.col1,'[^0-9]','','g')::int);
ERROR: invalid input syntax for integer: ""
nicht zu sehen ist, daß ich da ein Leerzeichen eingab.
Prinzipiell: man kann sowas machen (JOIN auf das Resultat einer Funktion und wildes rumCASTen), schön ist was anderes.
Ich könnte doch zuerst alle Einträge mit leerem String ausfiltern.
SELECT gfrgebaeude_details.gfrgebaeude_details_value FROM gfrgebaeude_details WHERE gfrgebaeude_details.gfrgebaeude_details_value != '' AND gfrgebaeude_details.gfrgebaeude_details_value != '0' AND gfrgebaeude_details.gfrgebaeude_details_value != '-1'
Dann mit der alten Abfrage kombinieren.
kombiniert
SELECT gfo_partner.name, gfrgebaeude_details.gfrgebaeude_details_value from gfo_partner left join gfrgebaeude_details on (gfo_partner.kontaktnummer = regexp_replace(gfrgebaeude_details.gfrgebaeude_details_value,'[^0-9]','','g')::int);"
test=> SELECT gfo_partner.name, gfrgebaeude_details.gfrgebaeude_details_value from gfo_partner left join gfrgebaeude_details on (gfo_partner.kontaktnummer = regexp_replace((SELECT gfrgebaeude_details.gfrgebaeude_details_value FROM gfrgebaeude_details WHERE gfrgebaeude_details.gfrgebaeude_details_value != '' AND gfrgebaeude_details.gfrgebaeude_details_value != '0' AND gfrgebaeude_details.gfrgebaeude_details_value != '-1'),'[^0-9]','','g')::int);
ERROR: more than one row returned by a subquery used as an expression
Das wäre, wenn es geht zwar nicht schön. Aber es würde reichen.