Concatenate different variables with number values in SQL Server

Question

I have different variables with string values, that I simply want to concatenate into one number.

For example I have the following variables:

pupil_id, class, term, id should result into Pupil_class_id
   23       8      3    23                         8323

I tried:

Select
    pupil_id, class, term, id,
    concat(class + term + id) as Pupil_class_id
from 
    school

and I tried

concat(class, term, id) as Pupil_class_id
from school

and I tried

concat('class', 'term' 'id') as Pupil_class_id
from school

I would have thought that solution 1 or 2 work, but they don't

Any suggestions?

No errors, but also no result, just 0

I can't figure out what you mean. Do you mean you want to concat the strings 'class', 'term' and 'id' into 'class_term_id'? Or did you want to concat a few numbers variables into a string? Or what. Can you make your question cleaner please? — Element Zero
– Element Zero, Commented Aug 5, 2019 at 22:45
I have: the variables class, term, id class =8, term = 3 , id= 23. I want to get the new variable: Pupil_class_id 8323 Essentially, what function adds the variables to create a new variable- not a sum, but just put all these numbers next to each other in one variable — Anna
– Anna, Commented Aug 6, 2019 at 2:06

marc_s · Accepted Answer · 2019-08-06 04:58:59Z

The approach with using a comma (,) as separator, and specifying the column names without single quotes is the way to do. I cannot reproduce any problems with that - not sure why you say this didn't work for you....

Try this (I'm using SQL Server 2016, but this should work from 2012 on):

DECLARE @Input TABLE (pupil_id INT, class INT, term INT, id INT)

INSERT INTO @Input (pupil_id, class, term, id)
VALUES (23, 8, 3, 23)

SELECT
    CONCAT(class, term, id)
FROM
    @Input

I get this output:

(No column name)
8323

which - if I understand correctly - is what you're looking for.

The only reason this might not work is if you're using an "old" database, e.g. if your database compatibility level is set to an earlier version of SQL Server. Was this database "upgraded" from a previous version of SQL Server?

Try this:

SELECT compatibility_level  
FROM sys.databases 
WHERE database_id = DB_ID()

What value do you get?? SQL Server 2012 should be "110" - do you have a lower value?

Collectives™ on Stack Overflow

Concatenate different variables with number values in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related