SQL Derived Number

Posted on 2011-10-12
Last Modified: 2012-05-12


I have 2 int columns in the same table that need to be concatenated to produce a unique 14 digit number.  Concatenating the 2 int's will produce a unique number everytime (as one of them is a primary key) but the issue is if the int's are large they may exceed the 14 digit requirement.

Hope you can help

Question by:nutnut
    LVL 9

    Expert Comment

    Try this

    declare @Digit1 int = 20111010
    declare @Digit2 int = 10000001
    select convert(bigint,convert(nvarchar,@Digit1) + convert(nvarchar,@Digit2))

    Open in new window

    LVL 9

    Accepted Solution

    The bigint data type is intended for use in cases where integer values might exceed the range supported by the int data type

    Author Comment

    Thanks but this may exceed the 14 digit requiremnt
    LVL 18

    Expert Comment

    spotted a problem

    if you add

    111 to 1111

    you get the same as adding

    1111 to 111

    even if the first is unique, the combination may not be

    how about

    declare @ikey int;
    declare @some_int int;

    select @ikey = 100000, @some_int = 99;

    select RIGHT('0000000000' + cast(@ikey as varchar(10)),10) + RIGHT('0000' + cast(@some_int as varchar(10)),4)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now