Concat_Ws() for SQL Server

Do we have a workaround for the T-SQL CONCAT_WS function?

First, SQL Server now supports CONCAT_WS(). So, using the built-in function is the simplest "work-around".

As an alternative:

select stuff( (coalesce(', ' + col1, '') +
               coalesce(', ' + col2, '') +
               coalesce(', ' + col3, '')
              ), 1, 2, '') as concat_ws

The separator and "2" need to be consistent, of course.

CONCAT function alternative in SQL Server 2008

Try casting the FLOOR_ID to text:

SELECT DISTINCT
    Project + '-' + CAST(FLOOR_ID AS VARCHAR(MAX)) AS value
FROM dbo.IMP_MODEL_GEOMETRY
WHERE Project LIKE '%".$test_term."%'";

The current error message mentions the string ' 22067-', which implies that the FLOOR_ID is the source of the problem.

SQL Server concat and convert

First, you don't need to explicitly convert, so:

concat(numbercolumn, textcolumn)

If this still converts to exponential, then convert to a decimal first. I'm not sure what you want things to look like but something like:

concat(convert(decimal(38, 10), numbercolumn), textcolumn)

You can also use format() or str() to convert the value to a string.

How can i do a concat() in SQL 2008

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.

concat two strings and put smaller string at first in sql server

Original answer:

If you need to order the input strings, not only by second character, STRING_AGG() is also an option:

DECLARE @a varchar(5) = '1923X'
DECLARE @b varchar(5) = '11459'

SELECT STRING_AGG(v.String, '#') WITHIN GROUP (ORDER BY v.String) AS Result
FROM (VALUES (@a), (@b)) v (String)

Output:

Result
11459#1923X

Update:

You changed the requirements (now the strings are stored in two columns), so you need a different statement:

SELECT 
   A, 
   B, 
   C = (
      SELECT STRING_AGG(v.String, '#') WITHIN GROUP (ORDER BY v.String)
      FROM (VALUES (A), (B)) v (String)
   )
FROM (VALUES ('1923X', '11459')) t (a, b)

Join strings in Oracle like concat_ws in SQL Server

A version that works in both Oracle and SQL Server is tricky because the only string concatenation function available is concat() with two arguments. But, you can do:

select trim('-' from
        concat(coalesce(c1, ''),
              concat(case when c2 is null then '' else concat('-', c2) end,
                     concat(case when c3 is null then '' else concat('-', c3) end,
                            case when c4 is null then '' else concat('-', c4) end
                           )
                    )
             ))

Here are the two db<>fiddles for SQL Server and Oracle.

---

Related Topics