1

I have to concat a few fields to generate a unique key, and in order to do that I want to combine 3 fields with different data types: integer, date and string.

What I used in SQL Server 2012:

CONCAT(PMEOBJECT.OBJECTID, '-', PMEOBJECT.FROMDATE, '-', PMEBUILDING.NAME) AS OBJECTIDKEY

Any ideas how to write this in SQL Server 2008?

edit: also NULLS have to be taken into account. editedit: I added ISNULL(PMEOBJECT.FROMDATE, '') in between to make it work. Thanks all.

titatovenaar
  • 309
  • 4
  • 12
  • 1
    You'll need to cast the non-strings to string and then use "+" to concat them. – smoore4 Oct 15 '18 at 13:23
  • 1
    You would not use `CONCAT` for this even in SQL Server 2012, because you really don't want to rely on whatever is the default conversion format for a `DATE` (which can vary with language). Use `CONVERT` explicitly. – Jeroen Mostert Oct 15 '18 at 13:41
  • 1
    Why not just use a surrogate key? – Eric Brandt Oct 15 '18 at 13:54
  • Keep in mind, CONCAT will implicitly convert NULL to an empty string. Using '+' does not do that. If you concatenate strings using '+', and any one of them is NULL, then the whole result will be NULL. – David Dubois Oct 15 '18 at 18:32
  • Thanks a lot for the replies. Now I actually did get into problems with the NULL's. I need to concatenate a few columns to create a unique key of which some of the columns have NULL values. How to deal with this? – titatovenaar Oct 30 '18 at 15:11

2 Answers2

2

You can use '+' to concatenate columns, but remember to cast non string types (int, date etc.) to a string type (for example varchar), otherwise an error would occurr (since SQL Sever would try to actually add values):

select cast(PMEOBJECT.OBJECTID as varchar(50)) + '-' 
     + cast(PMEOBJECT.FROMDATE as varchar(50)) + '-'
     + PMEBUILDING.NAME as OBJECTIDKEY

For date columns you'll probably want to define a custom format, so it is probably better to use CONVERT instead of CAST.

For example to obtain the classic yyyy-mm-dd hh:mi:ss ODBC format you can use this:

select cast(PMEOBJECT.OBJECTID as varchar(50)) + '-' 
     + convert(varchar(50), PMEOBJECT.FROMDATE, 120) + '-'
     + PMEBUILDING.NAME as OBJECTIDKEY
Andrea
  • 11,801
  • 17
  • 65
  • 72
  • 3
    [Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length). In this context it means `VARCHAR(30)`, but that's not at all obvious. – Jeroen Mostert Oct 15 '18 at 13:44
  • @JeroenMostert you are right, thank you for the advice. – Andrea Oct 15 '18 at 13:53
  • Makes a lot of sense and it works, for some of the columns.. However I now concatenate columns of which some of them have NULL values. How to concatenate columns without NULL overruling the entire column and other values in 2008? – titatovenaar Oct 30 '18 at 15:13
  • @titatovenaar What should happen when a NULL is found? A blank should appear (`objid--name`) or the hypen should also be removed (`objid-name`)? Can you please add some samples to your question? – Andrea Oct 30 '18 at 15:20
  • Found the answer, sorry for bringing this up again. I used: select cast(PMEOBJECT.OBJECTID as varchar(50)) + '-' + cast(ISNULL(PMEOBJECT.FROMDATE, '') as varchar(50)) + '-' + PMEBUILDING.NAME as OBJECTIDKEY – titatovenaar Oct 30 '18 at 15:30
0

You can also have type problem while using "+" though you will have to use sometimes convert(varchar, expression) :)

CestDede
  • 1
  • 2