Re: update table1 set column ...

From ar Mon Aug 22 09:13:02 1994 Return-Path: <ar> Received: by uranus.SoftwareEngineering (4.1/SMI-4.1) id AA00319; Mon, 22 Aug 94 09:13:02 +0200 Date: Mon, 22 Aug 94 09:13:02 +0200 From: ar (Achim Reiners) Message-Id: <9408220713.AA00319@uranus.SoftwareEngineering> To: Subject: Appending strings to columns - comp.databases.informix #5517 In article <332i0k$hp2@pheidippides.axion.bt.co.uk>, nailerp@leeds.syntegra.bt.co.uk writes: |> This bit of SQL doesn't work with Informix: |> |> update table1 set column1 = column1 update table1 set column1 = column1 || "a text string" |> |> Anybody know of a workaround? |> |> Paul Nailer Paul, I assume your column1 is defined ad CHAR(n). What happens when doing this update? The concatenation is done, but - of course - the "a text string" starts at position (n+1) in the concatenated string. This is independant of trailing blanks! When the assignment is done to column1 again, the string is truncated at position n. So "a text string" is removed again! Possible solutions for your problem: 1.) If the "old" length of column1 is shorter than the "n" , but you know how long (say of length "m") then you can assign: update table1 set coulmn1 = column1[1,m] || "a text string" or update table1 set column1[m+1,n] = "a text string". Of course the m+1 has to be calculated. 2.) If you want to append "a text string" after the last non-blank character of column1 then it is a bit more complicated. The function length(column1) will deliver the length to the last non-blank character. But the problem is that Informix is not able (I don't know why) to support something like column1 [ 1, length(column1) ]. Without such a construction I think it is not so easy to do what you want. Hope I could help a little bit. Achim -- ========================================================================== / Achim Reiners Software Engineering \\ | /M/A/I Deutschland GmbH | | Softwarezentrum Koeln Phone: +49 221 956400-40 | | Mathias-Brueggen-Str. 85 Fax: +49 221 956400-69 | \\ 50829 Koeln E-Mail: ar@mai.de / \\_________________________________________________________________________/ Info: I'll leave /M/A/I by the end of September but I'll stay in the Net!