SQL-Statment that add to strings

[Appel]

Hallo

i have a problem to add to string with a SQL statement.

My Table : Tablename NAMES:

| Name1 | Name2 | Name3 |
John Meyer

I search for a SQL statment that update Name3.

The statemant must add Name1 with Name2 and put the result into Name3


| Name1 | Name2 | Name3 |
John Meyer John Meyer


I have use :

update NAMES SET Name3=Name1+Nam e2

but it don't works.


Can anybody help me ?

[Knut Stolze]

Re: SQL-Statment that add to strings

Appel wrote:
[color=blue]
> Hallo
>
> i have a problem to add to string with a SQL statement.
>
> My Table : Tablename NAMES:
>
> | Name1 | Name2 | Name3 |
> John Meyer
>
> I search for a SQL statment that update Name3.
>
> The statemant must add Name1 with Name2 and put the result into Name3
>
>
> | Name1 | Name2 | Name3 |
> John Meyer John Meyer
>
>
> I have use :
>
> update NAMES SET Name3=Name1+Nam e2
>
> but it don't works.
>
> Can anybody help me ?[/color]

Have a look at the manual and read up on the function CONCAT.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Serge Rielau]

Re: SQL-Statment that add to strings

>>I have use :[color=blue][color=green]
>>
>>update NAMES SET Name3=Name1+Nam e2
>>
>>but it don't works.
>>
>>Can anybody help me ?[/color]
>
>
> Have a look at the manual and read up on the function CONCAT.
>[/color]
... which also happens to be the SQL standard and should work on all
major RDBMS. Also check out || which is the infix synonym for CONCAT().

Cheers
Serge

[amurchis]

Re: SQL-Statment that add to strings

Serge Rielau wrote:
[color=blue][color=green][color=darkred]
>>> I have use :
>>>
>>> update NAMES SET Name3=Name1+Nam e2
>>>
>>> but it don't works.
>>>
>>> Can anybody help me ?[/color]
>>
>>
>>
>> Have a look at the manual and read up on the function CONCAT.
>>[/color]
> .. which also happens to be the SQL standard and should work on all
> major RDBMS. Also check out || which is the infix synonym for CONCAT().
>
> Cheers
> Serge[/color]

Of course, if you just concatenate the two names, assuming they're
VARCHAR fields, you're going to to get "JohnMeyer" . In other words,
don't forget to put the space in their yourself. If they're CHAR
fields, then each name will be blank padded up to the length of the
field, so you'll likely get "John Meyer "
or something similar.

Just things to keep in mind.

[Andreas Reiners]

Re: SQL-Statment that add to strings

.... and to eliminate the blanks take a look at the "strip"-function.

Regards
Andreas
[color=blue]
> don't forget to put the space in their yourself. If they're CHAR
> fields, then each name will be blank padded up to the length of the
> field, so you'll likely get "John Meyer "
> or something similar.
>[/color]

[Jarl Hermansson]

Re: SQL-Statment that add to strings

"Appel" <appel@starne t-it.de> wrote in message news:<2ubjvjF29 b7aqU1@uni-berlin.de>...[color=blue]
> Hallo
>
> i have a problem to add to string with a SQL statement.
>
> My Table : Tablename NAMES:
>
> | Name1 | Name2 | Name3 |
> John Meyer
>
> I search for a SQL statment that update Name3.
>
> The statemant must add Name1 with Name2 and put the result into Name3
>
>
> | Name1 | Name2 | Name3 |
> John Meyer John Meyer
>
>
> I have use :
>
> update NAMES SET Name3=Name1+Nam e2
>
> but it don't works.
>
>
> Can anybody help me ?[/color]


UPDATE names SET name3 = TRIM(name1) || ' ' || TRIM(name2)

But do you really want to store the same information twice?

Its a waste of memory, but even more important is the risk of data
inconsistency. Typically somebody inserts or updates name1 or name2,
but forgets to update name3.

You can perhaps use a view:

CREATE VIEW namesview (name1, name2, name3) AS
SELECT name1, name2, TRIM(name1) || ' ' || TRIM(name2)
FROM names


HTH,
Jarl

[Knut Stolze]

Re: SQL-Statment that add to strings

Andreas Reiners wrote:
[color=blue]
> ... and to eliminate the blanks take a look at the "strip"-function.[/color]

There is no such function in DB2 UDB. You should rather use the RTRIM,
LTRIM, or REPLACE functions.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Andreas Reiners]

Re: SQL-Statment that add to strings

Knut Stolze <stolze@de.ibm. com> wrote in news:clst9s$lsu $1@fsuj29.rz.un i-
jena.de:
[color=blue]
> Andreas Reiners wrote:
>[color=green]
>> ... and to eliminate the blanks take a look at the "strip"-function.[/color]
>
> There is no such function in DB2 UDB. You should rather use the RTRIM,
> LTRIM, or REPLACE functions.
>[/color]

Really?

f.e.: http://publib.boulder.ibm.com/cgi-bi...nsqj10/3.2.93?
DT=200402161357 41

Regards
Andreas

[Knut Stolze]

Re: SQL-Statment that add to strings

Andreas Reiners wrote:
[color=blue]
> Knut Stolze <stolze@de.ibm. com> wrote in news:clst9s$lsu $1@fsuj29.rz.un i-
> jena.de:
>[color=green]
>> Andreas Reiners wrote:
>>[color=darkred]
>>> ... and to eliminate the blanks take a look at the "strip"-function.[/color]
>>
>> There is no such function in DB2 UDB. You should rather use the RTRIM,
>> LTRIM, or REPLACE functions.
>>[/color]
>
> Really?
>
> f.e.: http://publib.boulder.ibm.com/cgi-bi...nsqj10/3.2.93?
> DT=200402161357 41[/color]

Yes, really - at least if you are on DB2 UDB for Linux, Unix, and Windows.
Your link refers to DB2 for z/OS.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Troels Arvin]

Re: SQL-Statment that add to strings

On Thu, 28 Oct 2004 08:13:28 -0400, Serge Rielau wrote:
[color=blue][color=green]
>> Have a look at the manual and read up on the function CONCAT.
>>[/color]
> .. which also happens to be the SQL standard and should work on all
> major RDBMS. Also check out || which is the infix synonym for CONCAT().[/color]

CONCAT(...) is not specified in the SQL standard, only the ||
concatenation operator. CONCAT isn't even a reserved word.

--
Greetings from Troels Arvin, Copenhagen, Denmark