topic Re: How can I concatenate two fields having integer as data type in a SQL database table? in Alteryx Server Discussions

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

CEO — Wed, 28 Jul 2021 19:31:00 GMT

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.

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

BrandonB — Wed, 28 Jul 2021 19:40:32 GMT

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.

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

JagdeeshN — Wed, 28 Jul 2021 19:44:16 GMT

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

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

CEO — Wed, 28 Jul 2021 20:26:55 GMT

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?

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

CEO — Wed, 28 Jul 2021 20:29:52 GMT

@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

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

CEO — Wed, 28 Jul 2021 20:30:58 GMT

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

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

JagdeeshN — Thu, 29 Jul 2021 12:17:07 GMT

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