Concatenate fields with separator character using XML PATH

Question

Concatenate fields with separator character using XML PATH

Scott In Sydney

Hall of Fame

Points: 3331
More actions
December 7, 2016 at 10:35 pm

#323935

I found this on Stack Overflow but wanted to post here instead: http://stackoverflow.com/questions/19432370/concat-ws-for-sql-server
This works (i.e. gives me what I want):
DROP TABLE tmp.foo
CREATE TABLE tmp.foo (
id INT IDENTITY(1, 1) NOT NULL,
a VARCHAR(50),
b VARCHAR(50),
c VARCHAR(50),
d VARCHAR(50),
PRIMARY KEY (id)
);
INSERT INTO tmp.foo (a, b, c, d) VALUES ('a', 'b', 'c', 'd');
INSERT INTO tmp.foo (a, b, c, d) VALUES (NULL, 'b', NULL, 'd');
INSERT INTO tmp.foo (a, b, c, d) VALUES ('a', NULL, NULL, 'd');
INSERT INTO tmp.foo (a, b, c, d) VALUES (NULL, NULL, NULL, NULL);
SELECT
id,
a,b,c,d,
bar = STUFF(
(
SELECT '-' + CAST(v AS VARCHAR(1))
FROM (VALUES (a), (b), (c), (d)) AS v (v)
FOR XML PATH (''), TYPE
).value('.[1]', 'varchar(max)'),
1, 1, ''
)
FROM tmp.foo
ORDER BY id;
This works:
DROP TABLE tmp.foo
CREATE TABLE tmp.foo (
id INT IDENTITY(1, 1) NOT NULL,
a TINYINT,
b TINYINT,
c TINYINT,
d TINYINT
PRIMARY KEY (id)
);
INSERT INTO tmp.foo (a, b, c, d) VALUES (1, 2, 3, 4);
INSERT INTO tmp.foo (a, b, c, d) VALUES (NULL, 2, NULL, 4);
INSERT INTO tmp.foo (a, b, c, d) VALUES (1, NULL, NULL, 4);
INSERT INTO tmp.foo (a, b, c, d) VALUES (NULL, NULL, NULL, NULL);
SELECT
id,
a,b,c,d,
bar = STUFF(
(
SELECT '-' + CAST(v AS VARCHAR(MAX))
FROM (VALUES (a), (b), (c), (d)) AS v (v)
FOR XML PATH (''), TYPE
).value('.[1]', 'varchar(max)'),
1, 1, ''
)
FROM tmp.foo
ORDER BY id;
This fails:
DROP TABLE tmp.foo
CREATE TABLE tmp.foo (
id INT IDENTITY(1, 1) NOT NULL,
a TINYINT,
b VARCHAR(1),
c TINYINT,
d VARCHAR(1)
PRIMARY KEY (id)
);
-- single row so NULLs have no bearing on the error
INSERT INTO tmp.foo (a, b, c, d) VALUES (1, 'b', 3, 'd');
SELECT
id,
a,b,c,d,
bar = STUFF(
(
SELECT '-' + CAST(v AS VARCHAR(MAX))
FROM (VALUES (a), (b), (c), (d)) AS v (v)
FOR XML PATH (''), TYPE
).value('.[1]', 'varchar(max)'),
1, 1, ''
)
FROM tmp.foo
ORDER BY id;
With the error:
Msg 245, Level 16, State 1, Line 17
Conversion failed when converting the varchar value 'b' to data type tinyint.
My actual data needs to concatenate data with different data types.
I thought the two varchar(max) statements would convert the differing data types to varchar, but clearly I'm missing something simple.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

When using values of multiple types, SQL Server will always use Data Type Precedence (Transact-SQL). As you can see, INT is higher in the list than VARCHAR, so INT takes precendence. We both know that 'b' and 'd' aren't integers, so the conversion fails.

When using your for xml path, all the values are returning in a single column and a column CANNOT have fields of different types. Therefore Precedence applies. YOu're data would actually look like this:

v

---

1

b

3

d

Considering that 1 and 3 are of datatype INT, and 'b' and 'd' are VARCHARs, that table can't exist.

This, instead, would work for you:

DROP TABLE tmp.foo

CREATE TABLE tmp.foo (

id INT IDENTITY(1, 1) NOT NULL,

a TINYINT,

b VARCHAR(1),

c TINYINT,

d VARCHAR(1)

PRIMARY KEY (id)

);

-- single row so NULLs have no bearing on the error

INSERT INTO tmp.foo (a, b, c, d) VALUES (1, 'b', 3, 'd');

SELECT

id,

a,b,c,d,

bar = STUFF(

(

SELECT '-' + v

FROM (VALUES (CAST(a AS VARCHAR(MAX))), (CAST(b AS VARCHAR(MAX))), (CAST( c AS VARCHAR(MAX))), (CAST(d AS VARCHAR(MAX)))) AS v (v)

FOR XML PATH (''), TYPE

).value('.[1]', 'varchar(max)'),

1, 1, ''

)

FROM tmp.foo

ORDER BY id;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

What Thom said, just don't use varchar(MAX) to optimize the resources. 😉

Just keep the length of the longest type (in this case 1).

CREATE TABLE foo (

id INT IDENTITY(1, 1) NOT NULL,

a TINYINT,

b VARCHAR(1),

c TINYINT,

d VARCHAR(1)

PRIMARY KEY (id)

);

-- single row so NULLs have no bearing on the error

INSERT INTO foo (a, b, c, d) VALUES (1, 'b', 3, 'd');

SELECT

id,

a,b,c,d,

bar = STUFF(

(

SELECT '-' + v

FROM (VALUES (CAST(a AS VARCHAR(1))), (b), (CAST( c AS VARCHAR(1))), (d)) AS v (v)

FOR XML PATH (''), TYPE

).value('.[1]', 'varchar(max)'),

1, 1, ''

)

FROM foo

ORDER BY id;

DROP TABLE foo;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

On a similar note, I really like CONCAT (available for SQL Serve 2012+) because it's cleaner and circumvents the need to do any casting/converting when dealing with numbers.

DECLARE @table TABLE (c1 int, c2 int);

INSERT @table VALUES (1,2);

-- Before CONCAT()

SELECT CAST(c1 AS char(1))+CAST(c2 AS char(1)) FROM @table;

-- Using CONCAT()

SELECT CONCAT(1,2) FROM @table;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001