none
I want to add 01 to number as 1000, 1002 as a suffix RRS feed

  • 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)


    Please mark as answered, If you feel happy with this answer.

    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
    Moderator
  • DECLARE @EmployeeID int = 1002
    SELECT STR(@EmployeeID,LEN(@EmployeeID)) + '-1'
    
    SELECT CONCAT(@EmployeeID,'-1')


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, December 8, 2017 8:45 AM