p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: concatenating 2 fields in SQL query


Message #1 by Colin.Montgomery@C... on Wed, 5 Dec 2001 11:23:17 -0000
Hi



Hopefully an easy one for you guys:



I want to return the value from Field1 & Field2 AS myFieldName, but am

unsure of the syntax.  I imagine it something like:



SELECT (Field1 & Field2) AS myField

FROM myTable



It would be great if I could insert some short text or HTML in the resulting

Field, between the two values (so i'd get "myField1Value:

<b>myField2Value"), but I don't think this will be possible.  (I have to

write it as a stored querydef in Access 97, and I can't play about with the

recordset).



Can anyone help me out?



Thanks in advance,

Colin





*******



This message and any attachment are confidential and may be privileged or otherwise protected from disclosure.  If you are not the
intended recipient, please telephone or email the sender and delete this message and any attachment from your system.  If you are
not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.



For further information about Clifford Chance please see our website at http://www.cliffordchance.com or refer to any Clifford
Chance office.

Message #2 by "Kim Iwan Hansen" <kimiwan@k...> on Wed, 5 Dec 2001 17:21:55 +0100
Hi Colin,



String concatenation in access is similar to that in asp:

SELECT (Field1 & ': <b>' & Field2) AS myField FROM myTable



Different databases use different syntax for this:

SQL Server: (Field1 + ' ' + Field2) AS myField)

MySQL: CONCAT(Field1,' ',Field2)

Oracle & DB2: (Field1 || ' ' || Field2)



-Kim





-----Original Message-----

From: Colin.Montgomery@C...

[mailto:Colin.Montgomery@C...]

Sent: 5. december 2001 12:23

To: ASP Databases

Subject: [asp_databases] concatenating 2 fields in SQL query





Hi



Hopefully an easy one for you guys:



I want to return the value from Field1 & Field2 AS myFieldName, but am

unsure of the syntax.  I imagine it something like:



SELECT (Field1 & Field2) AS myField

FROM myTable



It would be great if I could insert some short text or HTML in the resulting

Field, between the two values (so i'd get "myField1Value:

<b>myField2Value"), but I don't think this will be possible.  (I have to

write it as a stored querydef in Access 97, and I can't play about with the

recordset).



Can anyone help me out?



Thanks in advance,

Colin





*******



This message and any attachment are confidential and may be privileged or

otherwise protected from disclosure.  If you are not the intended recipient,

please telephone or email the sender and delete this message and any

attachment from your system.  If you are not the intended recipient you must

not copy this message or attachment or disclose the contents to any other

person.



For further information about Clifford Chance please see our website at

http://www.cliffordchance.com or refer to any Clifford Chance office.








$subst('Email.Unsub')






$subst('Email.Unsub').



Message #3 by "Tomm Matthis" <matthis@b...> on Wed, 5 Dec 2001 11:51:55 -0500
Use the "+" instead of "&" when concatenating in SQL.



-- Tomm



> -----Original Message-----

> From: Colin.Montgomery@C...

> [mailto:Colin.Montgomery@C...]

> Sent: Wednesday, December 05, 2001 6:23 AM

> To: ASP Databases

> Subject: [asp_databases] concatenating 2 fields in SQL query

>

>

> Hi

>

> Hopefully an easy one for you guys:

>

> I want to return the value from Field1 & Field2 AS myFieldName, but am

> unsure of the syntax.  I imagine it something like:

>

> SELECT (Field1 & Field2) AS myField

> FROM myTable

>

> It would be great if I could insert some short text or HTML in the resulting

> Field, between the two values (so i'd get "myField1Value:

> <b>myField2Value"), but I don't think this will be possible.  (I have to

> write it as a stored querydef in Access 97, and I can't play about with the

> recordset).

>

> Can anyone help me out?

>

> Thanks in advance,

> Colin

>

>

> *******

>

> This message and any attachment are confidential and may be

> privileged or otherwise protected from disclosure.  If you are not

> the intended recipient, please telephone or email the sender and

> delete this message and any attachment from your system.  If you

> are not the intended recipient you must not copy this message or

> attachment or disclose the contents to any other person.

>

> For further information about Clifford Chance please see our

> website at http://www.cliffordchance.com or refer to any Clifford

> Chance office.

>

>




> $subst('Email.Unsub')

>




> $subst('Email.Unsub').



Message #4 by "Prakash" <warana2000@y...> on Fri, 7 Dec 2001 12:34:45
> Hi

> 

> Hopefully an easy one for you guys:

> 

> I want to return the value from Field1 & Field2 AS myFieldName, but am

> unsure of the syntax.  I imagine it something like:

> 

> SELECT (Field1 & Field2) AS myField

> FROM myTable

> 

> It would be great if I could insert some short text or HTML in the 

resulting

> Field, between the two values (so i'd get "myField1Value:

> <b>myField2Value"), but I don't think this will be possible.  (I have to

> write it as a stored querydef in Access 97, and I can't play about with 

the

> recordset).

> 

> Can anyone help me out?

> 

> Thanks in advance,

> Colin

> 

> 

> *******

> 

> This message and any attachment are confidential and may be privileged 

or otherwise protected from disclosure.  If you are not the intended 

recipient, please telephone or email the sender and delete this message 

and any attachment from your system.  If you are not the intended 

recipient you must not copy this message or attachment or disclose the 

contents to any other person.

> 

> For further information about Clifford Chance please see our website at 

http://www.cliffordchance.com or refer to any Clifford Chance office.

Message #5 by "S. Sridhar" <oesridhar@e...> on Fri, 7 Dec 2001 08:00:59 -0500
What I would do here is to solve this with a Stored Procedure using the

SELECT INTO syntax.

Pseudo-code:



SELECT Field1, Field2 INTO :myField1 :myField2

    FROM myTable



The stored procedure would have :myField1 and :myField2 as OUT parameters.

If you want to play with myField1 and myField2 in the SP, you could then

concatenate these two and then use them in a new SELECT statement -- as you

appear to indicate in your note.



--sridhar







----- Original Message -----

From: "Prakash" <warana2000@y...>

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, December 07, 2001 12:34 PM

Subject: [asp_databases] Re: concatenating 2 fields in SQL query





> > Hi

> >

> > Hopefully an easy one for you guys:

> >

> > I want to return the value from Field1 & Field2 AS myFieldName, but am

> > unsure of the syntax.  I imagine it something like:

> >

> > SELECT (Field1 & Field2) AS myField

> > FROM myTable

> >

> > It would be great if I could insert some short text or HTML in the

> resulting

> > Field, between the two values (so i'd get "myField1Value:

> > <b>myField2Value"), but I don't think this will be possible.  (I have to

> > write it as a stored querydef in Access 97, and I can't play about with

> the

> > recordset).

> >

> > Can anyone help me out?

> >

> > Thanks in advance,

> > Colin





Message #6 by Colin.Montgomery@C... on Fri, 7 Dec 2001 13:09:43 -0000
thanks to everyone on this - I ended up figuring it out for myself:



SELECT "<b>" & Field1 & ":</b><br>" & Field2 AS myFieldName



What I would like is to only include everything before Field2 IF Field1 is

not Empty/Null   (think the field can contain zero length strings).

Otherwise I get a blank line in the HTML table above the Field2 value.



Any ideas?



Thanks,

Colin



-----Original Message-----

From: S. Sridhar [mailto:oesridhar@e...]

Sent: 07 December 2001 13:01

To: ASP Databases

Subject: [asp_databases] Re: concatenating 2 fields in SQL query





What I would do here is to solve this with a Stored Procedure using the

SELECT INTO syntax.

Pseudo-code:



SELECT Field1, Field2 INTO :myField1 :myField2

    FROM myTable



The stored procedure would have :myField1 and :myField2 as OUT parameters.

If you want to play with myField1 and myField2 in the SP, you could then

concatenate these two and then use them in a new SELECT statement -- as you

appear to indicate in your note.



--sridhar







----- Original Message -----

From: "Prakash" <warana2000@y...>

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, December 07, 2001 12:34 PM

Subject: [asp_databases] Re: concatenating 2 fields in SQL query





> > Hi

> >

> > Hopefully an easy one for you guys:

> >

> > I want to return the value from Field1 & Field2 AS myFieldName, but am

> > unsure of the syntax.  I imagine it something like:

> >

> > SELECT (Field1 & Field2) AS myField

> > FROM myTable

> >

> > It would be great if I could insert some short text or HTML in the

> resulting

> > Field, between the two values (so i'd get "myField1Value:

> > <b>myField2Value"), but I don't think this will be possible.  (I have to

> > write it as a stored querydef in Access 97, and I can't play about with

> the

> > recordset).

> >

> > Can anyone help me out?

> >

> > Thanks in advance,

> > Colin










$subst('Email.Unsub').





*******



This message and any attachment are confidential and may be privileged or otherwise protected from disclosure.  If you are not the
intended recipient, please telephone or email the sender and delete this message and any attachment from your system.  If you are
not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.



For further information about Clifford Chance please see our website at http://www.cliffordchance.com or refer to any Clifford
Chance office.

Message #7 by "Ken Schaefer" <ken@a...> on Sat, 8 Dec 2001 14:28:07 +1100
Use IIF()



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: <Colin.Montgomery@C...>

Subject: [asp_databases] Re: concatenating 2 fields in SQL query





: thanks to everyone on this - I ended up figuring it out for myself:

:

: SELECT "<b>" & Field1 & ":</b><br>" & Field2 AS myFieldName

:

: What I would like is to only include everything before Field2 IF Field1 is

: not Empty/Null   (think the field can contain zero length strings).

: Otherwise I get a blank line in the HTML table above the Field2 value.

:

: Any ideas?



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.




  Return to Index