How can I concatenate two fields having integer as data type in a SQL database table?

[CEO]

Hello, I am creating a table and I would want two particular fields to concatenate to form a 3rd field. Take for instance:

___________________________________________

class int not null,

studentNo int unique IDENTITY(100000,1) Primary Key,

StudentCode...

_____________________________________________

Class field will be 3-digits integer. StudentNo will be automatically generated, starting from 10,000 StudentCode (I want it to contain the class 3-digits for each student and also the studentNo. It will start with the 3-digits of the class. i.e to concatenate the class value and the studentNo value) I try as much to explain because I want explicit suggestion. If you are proficient with database (MySql or Microsoft Sql), and you know about this, then kindly help. Please help with a line or lines of codes.

[BrandonB]

In Alteryx you can wrap a column with ToString([ColumnName]) to effectively cast it to a string. You can also use a select tool if you want to modify the column data type. 

[JagdeeshN]

Hi @CEO ,

You can do this in two was.

1. Concatenate within Alteryx:- Please refer to the attached sample workflow on how to do this. Within the workflow i have used a Text Input just for showcasing. You will need to replace this with your input to the actual database.

2. Concatenate using SQL:- You can do this by executing a query similar to one described below.

SELECT CONCAT(c.Class, ':', c.studentNo) AS studentCode,
c.*
FROM `tablename` c;

Hope this helps!

Best,

Jagdeesh 

[CEO]

Thanks for your time. But using the SELECT command would only make it effective for that command. I would want it my table in the database to be programmed with that. I want the studentCode field to be affected by that command whenever a record or an update is made. How do I go about it please without using a SELECT statement?

[CEO]

@jagdeeshN

...and I will appreciate if you kindly write the line of code as if you're teaching a beginner, so I can study it and understand. I don't understand what you wrote there

[CEO]

I want to write the code in my database server, not in my application code. 

[JagdeeshN]

Hi @CEO ,

Please check the below link. It describes how to create virtual calculated columns within a table.

https://www.sqlshack.com/an-overview-of-computed-columns-in-sql-server/

Hope this helps.

Best,

Jagdeesh