Best way to concatenate two fields having integer datatype. Can you help please?

[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)
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.

[SGaist]

Hi,

See the CONCAT string function.

See here for a simple tutorial on it.

[CEO.]

@SGaist alright. Thanks.

[CEO.]

@SGaist what I'm asking for is integer data types, not varcharc

[JonB]

@CEO
You can't just "concatenate" integer values, or at least you'd have to define what you mean.

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)

This is presumably a string, made from concatenating the string digit representations. And that is what @SGaist is suggesting SQL CONCAT for. If you don't want a string as the concatenation then what do you want?

[SGaist]

Use CAST to convert.

[mchinand]

You could use a database trigger to automatically populate that 3rd column after an INSERT or UPDATE. Alternatively, since the third value can be generated from other columns you can generate it on the fly in the SELECT statement.

e.g., SELECT class, studentNo, CONCATENATE(class, studentNo) AS StudentCode from studytable

You'll probably have to cast the two integers to string/text before the concatenation.

[SimonSchroeder]

I am not sure if what you are trying to do is a good approach.

Lets assume that studentNo is in the range of 10000 to 99999 and you want the combined field to be an integer as well. Then you can multiply the 3-digit classCode by 100000 and add the two: studentCode = classCode*100000+studentNo

However, you will run into trouble when studentNo exceeds 99999. You could shift it by one digit, but this will always fail eventually. And it is a design that will be hard to change later.

I think this statement is most problematic:

I would want two particular fields to concatenate to form a 3rd field.

It is not a good idea to put redundancy into your database. You can always cheaply create the concatenated studentCode. There is no reason to have it saved explicitly. Whenever you need the studenCode, just write a query that computes it from the other two fields. This makes it easier to change it in the future when you run out of digits. It is not so easy to keep your database consistent when you need to change that 3rd field in the future.

I believe that databases also have views to tables (I have never used them so far) where you could put your 3rd column. Future changes would then only have to change the definition of the view.

Short advice: Don't duplicate data in your database. Don't make consistency hard in your databases. Previous answers still apply in how you can write your queries to retrieve the studentCode.

[CEO.]

@SimonSchroeder you have a valid point.
I will appreciate you kindly write the code out to execute your suggestion. Let me see it plz

[SimonSchroeder]

@CEO said in Best way to concatenate two fields having integer datatype. Can you help please?:

I will appreciate you kindly write the code out to execute your suggestion.

I can't do any better than what @mchinand already suggested:

SELECT class, studentNo, CONCATENATE(class, studentNo) AS StudentCode FROM studytable

You'll probably have to cast the two integers to string/text before the concatenation.

If you want to use views, I don't have any experience with these myself and don't know the syntax.