AW: Concatenation in SQL

Posted in 2000

Topics: Stored Procedures & SPL, Data Types & Schema Design

Mickler, Frank — Tue, 14 Mar 2000 08:13:08 +0100 — source: Informix-list mailing list archive (1991-1998)

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BF8D84.BFF0B2F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable If you're using a version less 7.3x you can use the following = procedure: Create Procedure NVL ( P_Value VarChar(255), P_Null_Return VarChar(255) ) Returning VarChar(255); If( P_Value Is Null ) Then Return P_Null_Return; Else Return P_Value; End If; End Procedure; Frank Mickler Twinsoft Germany frank.mickler@twinsoft.de ##:-----Urspr=FCngliche Nachricht----- ##:Von: Sujit.Pal@bankofamerica.com = [mailto:Sujit.Pal@bankofamerica.com] ##:Gesendet: Montag, 13. M=E4rz 2000 18:55 ##:An: Lawrence Choy ##:Cc: informix-list@iiug.org ##:Betreff: Re: Concatenation in SQL ##: ##: ##: ##: ##:Lawrence ##: ##:If you have 7.31 you can use nvl() to replace the null=20 ##:middle name with some ##:other character, say space. Frex: ##: ##:SELECT firstname || nvl(middlename,"") || lastname ##: FROM employee ##: ##:HTH ##:Sujit ##: ##: ##: ##: ##: ##:Lawrence Choy <choyls@pd.jaring.my> on 03/11/2000 08:00:07 AM ##: ##:Please respond to Lawrence Choy <choyL@logica.com> ##: ##:To: informix-list@iiug.org ##:cc: ##:Subject: Concatenation in SQL ##: ##: ##: ##:Hi gurus, ##: ##:I have been trying to concatenate 3 character-datatype=20 ##:columns together ##:using '||' operator in a simple SQL statement as this: ##: ##:SELECT firstname || middlename || lastname ##: FROM employee ##: ##:However, I found out that if either one of the column is a=20 ##:NULL or empty ##:value, the result returned is nothing at all. Can you gurus=20 ##:help me to solve ##:this puzzle 'coz I am really running out of ideas. Don't ask=20 ##:me to RTFM, I ##:have read almost every section on the SQL. :( ##: ##:Or, is it possible to detect if let's say middlename column=20 ##:is NULL, I ##:replaced it with another value. May I know how to do that too? ##: ##:Please, please help me... ##: ##:Cheers, ##:Lawrence Choy ##: ##: ##: ##: ##: ##: ##: ##: ------_=_NextPart_001_01BF8D84.BFF0B2F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12"> <TITLE>AW: Concatenation in SQL</TITLE> </HEAD> <BODY> If you're using a version less 7.3x you can use the = following procedure: Create Procedure NVL   (   = P_Value         = VarChar(255),   P_Null_Return   VarChar(255)   )   Returning VarChar(255);   If( P_Value Is Null ) Then     Return P_Null_Return;   Else     Return P_Value;   End If; End Procedure; Frank Mickler Twinsoft Germany frank.mickler@twinsoft.de ##:-----Urspr=FCngliche Nachricht----- ##:Von: Sujit.Pal@bankofamerica.com [<A = HREF=3D"mailto:Sujit.Pal@bankofamerica.com">mailto:Sujit.Pal@bankofameri= ca.com</A>] ##:Gesendet: Montag, 13. M=E4rz 2000 18:55 ##:An: Lawrence Choy ##:Cc: informix-list@iiug.org ##:Betreff: Re: Concatenation in SQL ##: ##: ##: ##: ##:Lawrence ##: ##:If you have 7.31 you can use nvl() to replace the = null ##:middle name with some ##:other character, say space. Frex: ##: ##:SELECT firstname || nvl(middlename,"") = || lastname ##:  FROM employee ##: ##:HTH ##:Sujit ##: ##: ##: ##: ##: ##:Lawrence Choy <choyls@pd.jaring.my> on = 03/11/2000 08:00:07 AM ##: ##:Please respond to Lawrence Choy = <choyL@logica.com> ##: ##:To:   informix-list@iiug.org ##:cc: ##:Subject:  Concatenation in SQL ##: ##: ##: ##:Hi gurus, ##: ##:I have been trying to concatenate 3 = character-datatype ##:columns together ##:using '||' operator in a simple SQL statement as = this: ##: ##:SELECT firstname || middlename || lastname ##:  FROM employee ##: ##:However, I found out that if either one of the = column is a ##:NULL or empty ##:value, the result returned is nothing at all. Can = you gurus ##:help me to solve ##:this puzzle 'coz I am really running out of = ideas. Don't ask ##:me to RTFM, I ##:have read almost every section on the SQL. = :( ##: ##:Or, is it possible to detect if let's say = middlename column ##:is NULL, I ##:replaced it with another value. May I know how to = do that too? ##: ##:Please, please help me... ##: ##:Cheers, ##:Lawrence Choy ##: ##: <B

AW: Concatenation in SQL

Related threads

Your privacy choices