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

Answer 1

 

Try

 

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

Answer 2

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.

Answer 3

The MSAccess equivalent of CONVERT(VARCHAR, NumberColumn) is CStr(NumberColumn)

Answer 4

 

ok thanks, guess there's no way of same sql working on both, cheers

Answer 5

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.