-1

I have this statement, which works in SQL Server 2017:

REPLACE(CONCAT(NOM_TIPO_QUEBRA_ORDEM, CHAR(13), DSC_TIPO_QUEBRA_ORDEM, CHAR(13), HST_ORDEM_FILA_MOVIMENTO), CHAR(13) + CHAR(13), '') as justificativa_quebra

How can I do this same in SQL Server 2008, which does not support CONCAT?

REPLACE(SELECT NOM_TIPO_QUEBRA_ORDEM, CHAR(13) + DSC_TIPO_QUEBRA_ORDEM, CHAR(13) + HST_ORDEM_FILA_MOVIMENTO), CHAR(13) + CHAR(13), '' as justificativa_quebra
Thom A
  • 88,727
  • 11
  • 45
  • 75
Pedro Victor Ferreira
  • 19
  • 3

1 Answers1

1

CONCAT(a,b,c) is basically just syntactic sugar for COALESCE(RTRIM(a),'') + COALESCE(RTRIM(b),'') + COALESCE(RTRIM(c),'') (example).

So:

REPLACE
(
   CONCAT
   (
     NOM_TIPO_QUEBRA_ORDEM, CHAR(13),
     DSC_TIPO_QUEBRA_ORDEM, CHAR(13), 
     HST_ORDEM_FILA_MOVIMENTO
   ),  
CHAR(13) + CHAR(13), '') as justificativa_quebra

Becomes:

REPLACE(
    COALESCE(RTRIM(NOM_TIPO_QUEBRA_ORDEM),'') + char(13)
  + COALESCE(RTRIM(DSC_TIPO_QUEBRA_ORDEM),'') + char(13)
  + COALESCE(RTRIM(HST_ORDEM_FILA_MOVIMENTO), ''), 
CHAR(13) + CHAR(13), '') as justificativa_quebra

Next, send some feedback up the chain that using a database platform so famously out of support is just not a great idea.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • thank u so much, Aaron, and, unfortunately i have to consume a view in a old database of another company. – Pedro Victor Ferreira May 23 '22 at 13:57
  • "just syntactic sugar" no it isn't: nulls and conversion come to mind – Charlieface May 23 '22 at 14:08
  • 1
    @Charlieface It kind of is, I only handled the NULL aspect here, but it still replaces the need to do those things; my point wasn't to be 100% authoritative about all of the things CONCAT does, just that you can still accomplish them without CONCAT. If the question had enough information about the underlying types I would have addressed conversion too. I am just trying to solve the OP's immediate problem here, not write the wikipedia entry on CONCAT. – Aaron Bertrand May 23 '22 at 14:14