CODEWITHSUNDEEP
sql-serversql-server-2012
Hafiz M Taha Waseem
Hafiz M Taha Waseem

Reputation: 71

How to use Mask in columns in SQL Server 2012

Is it possible to add mask on a column(int or varhchar data type) in SQL Server 2012?

I have a 'Main_Code' column,I want to save the code like '01' or '0001' instead of '1'.I read about the Dynamic Data Mask,but found that it is only available in SQL Server 2016.

So,what should I do for this format in SQL Server 2012? And is it necessary to use 'varchar' for mask or 'int' could also be used?

Upvotes: 2

Views: 1756

Answers (1)

PSK
PSK

Reputation: 17943

'0001' is not an Integer, to store such data you need to have VARCHAR as your data type.

Case 1: If you can't change the data type of the table from int to varchar, in that case you need to do the formatting in your application only. Let the number be stored in integer and do the required formatting in fronted.

Note: If it is all about formatting, you should not change your data type. Let it be int only, you do your formatting on front end or in the select statement.

Case 2: If you can change the datatype to VARCHAR, in that case you can use following statement in your insert statement.

INSERT INTO [Main_Code] (Id) --Mention other column
VALUES(FORMAT(1,'000#')) --Mention other values

If you want this to be a custom Auto-Generated Sequences, please follow the example given here

Upvotes: 4

Related Questions