# I want to add 01 to number as 1000, 1002 as a suffix

• ### Question

• Hello team

My employeeID is an integer and I want to add suffix -1. How can I do that?

Regards,

Sky

CloudsInSky

Thursday, December 7, 2017 9:57 PM

### All replies

• Hi,

You can do it like this :

DECLARE @EmployeeID INT = 1002

SELECT CAST(CAST(@EmployeeID AS VARCHAR(10))+'01' AS INT)

Thursday, December 7, 2017 10:04 PM
• >> My employeeID is an integer and I want to add suffix -1. How can I do that? <<

Please get the basic book on data modeling and read it. An employee ID is an identifier; identifiers are never numeric because you don't do any math with them. This should be a character string, perhaps made of digits. In which case adding a "xxxxx-1" is a simple matter of and alter table and string concatenation.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Thursday, December 7, 2017 10:33 PM
• use a simple string concatenation  to meet your requirement.

for this you need to convert your int  field in to varchar and then perform concatenation.

declare @id int
set @id = 100

select CONVERT(varchar(10), @id)+ '-1'
select CONVERT(varchar(10), @id)+ '1'
select CONVERT(varchar(10), @id)+ '2'

Thursday, December 7, 2017 10:44 PM
• CONCAT (for 2012+) can also do it, your integer will be implicitly converted
Thursday, December 7, 2017 10:49 PM
• Hi Sky,

Per the title of the post, you mean to want this?

declare @employeeID1 int
declare @employeeID2 int

set @employeeID1=1000
set @employeeID2=1002

select @employeeID1 as v1_1,@employeeID1*100+1 as v1_2,@employeeID2 as v2_1,@employeeID2*100+1 as v2_2

Best Regards,

Will

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Friday, December 8, 2017 8:35 AM
• DECLARE @EmployeeID int = 1002
SELECT STR(@EmployeeID,LEN(@EmployeeID)) + '-1'

SELECT CONCAT(@EmployeeID,'-1')

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog