none
Concatenate 2 fields

    Question

  •  

    Is there any way of concatenating 2 fields and a seperator, an id (integer) then "|||" then a nvarchar field, it would make my life much easier if I could come up with SQL that works in ms access as well as sql server.. Thanks
    Wednesday, February 13, 2008 11:37 AM

Answers

  •  

    Try

     

    SELECT CONVERT(nvarchar, id) + N'|||' + nvarcharColumn

    Wednesday, February 13, 2008 11:39 AM
  • To concatenate a Numeric or Date column you need to convert it to VARCHAR or NVARCHAR. You can use the CONVERT() or CAST() functions to do that.

    for example

    SELECT CONVERT(VARCHAR(20), NumberColumn) + '|' + CAST(DateColumn AS VARCHAR(20))

    The first column uses CONVERT() function and the second uses CAST() function.In most of the cases, you can use either of them.
    Wednesday, February 13, 2008 11:58 AM
    Moderator

All replies

  •  

    Try

     

    SELECT CONVERT(nvarchar, id) + N'|||' + nvarcharColumn

    Wednesday, February 13, 2008 11:39 AM
  • To concatenate a Numeric or Date column you need to convert it to VARCHAR or NVARCHAR. You can use the CONVERT() or CAST() functions to do that.

    for example

    SELECT CONVERT(VARCHAR(20), NumberColumn) + '|' + CAST(DateColumn AS VARCHAR(20))

    The first column uses CONVERT() function and the second uses CAST() function.In most of the cases, you can use either of them.
    Wednesday, February 13, 2008 11:58 AM
    Moderator
  • The MSAccess equivalent of CONVERT(VARCHAR, NumberColumn) is CStr(NumberColumn)
    Wednesday, February 13, 2008 12:02 PM
    Moderator
  •  

    ok thanks, guess there's no way of same sql working on both, cheers
    Wednesday, February 13, 2008 12:05 PM
  • Just to follow up on Jacob's comment I remembered that you will need to add a coalesce or ISNULL round each column if they are nullable as any string concatenated with null results in NULL.

     

    To be honest it is a long time since I looked at MS Access and, therefore, cannot say whether there is a single SQL statement which would satisfy both SQL Server and MS Access. I believe that the more recent versions of MSAccess use SQL Server under the hood but I may be wrong.

     

    Wednesday, February 13, 2008 12:08 PM