none
Not able to generate a unique number from combination of varchar fields

    Question

  • I am trying to generate a bigint number based on concatenation of different fields. As per below query.But it is generating the same number for different trucknbr,depdt ,Shiporig,Shipdest combination in some cases

     Select trucknbr,depdt ,Shiporig,Shipdest,
    CONVERT(BIGINT,( convert(varbinary,cast(trucknbr as varchar(4)))+ convert(varbinary, depdt) + CONVERT(VARBINARY, Shiporig) + CONVERT(VARBINARY, Shipdest)  )) 
    from viewname where DepDt = '2014-04-01'
    and ShipOrig ='PSP'

    5180 2014-04-01 PSP SFO 4038409809627924047
    5204 2014-04-01 PSP IAH 4038409809627267400
    5268 2014-04-01 PSP SFO 4038409809627924047

    In database the above first 4 columns are stored as INT, date,char(3), char(3)

    What modifications can I make to the query to generate unique numbers in 5th column. AS one can see the trucknumbers are different for row number 1 & 3 with same dep date and Shiporig and Shipdest.

    Thank you



    prashb

    • Moved by Olaf HelperMVP Friday, March 31, 2017 12:50 PM Moved from "Database Engine" to a more related forum
    Monday, March 20, 2017 9:05 PM

All replies

  • You havent posted complete data. Post DDL and sample data scripts.

    I presume concatenation may have resulted in data type length larger than defined , so some of data is truncated and giving same initial data. Unless, we have actual data it would be difficult to provide solution.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, March 20, 2017 9:18 PM
  • Thanks Sarat for your Reply.

    I think the sql select statement I had put in my previous  post was not visible. I am posting it again. I am not inserting any records.Just retrieving data from database using Select statement on INMEMORY table. The datatype of the 4 columns I am concatenating to generate a bigint number is INT, date,char(3) and char(3). The version of the database is 

    Microsoft SQL Server 2014 (SP1-CU3) (KB3094221) - 12.0.4427.24 (X64) 
    Oct 10 2015 17:18:26 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

    Select trucknbr,depdt ,Shiporig,Shipdest,
    CONVERT(BIGINT,( convert(varbinary,cast(trucknbr as varchar(4)))+ convert(varbinary, depdt) + CONVERT(VARBINARY, Shiporig) + CONVERT(VARBINARY, Shipdest)  )) 
    from viewname where DepDt = '2014-04-01'
    and ShipOrig ='PSP'

    The view is based on inmemory table.

    Thank you


    prashb

    Monday, March 20, 2017 9:42 PM
  • Thanks Sarat for your Reply.

    I think the sql select statement I had put in my previous  post was not visible. I am posting it again. I am not inserting any records.Just retrieving data from database using Select statement on INMEMORY table. The datatype of the 4 columns I am concatenating to generate a bigint number is INT, date,char(3) and char(3). The version of the database is 

    Microsoft SQL Server 2014 (SP1-CU3) (KB3094221) - 12.0.4427.24 (X64) 
    Oct 10 2015 17:18:26 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

    Select trucknbr,depdt ,Shiporig,Shipdest,
    CONVERT(BIGINT,( convert(varbinary,cast(trucknbr as varchar(4)))+ convert(varbinary, depdt) + CONVERT(VARBINARY, Shiporig) + CONVERT(VARBINARY, Shipdest)  )) 
    from viewname where DepDt = '2014-04-01'
    and ShipOrig ='PSP'

    The view is based on inmemory table.

    Thank you


    prashb

    Can you post if sample output of this query?

     
    Select trucknbr,depdt ,Shiporig,Shipdest
    
    from viewname where DepDt = '2014-04-01'
    and ShipOrig ='PSP'

    Why are you converting the values to varbinary and then BIGINT?

    From example given in post, you have reached max no of digits supported by BIGINT i.e. 4038409809627924047.

    Instead of bigint , can you try VARCHAR(20) or so and check?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, March 20, 2017 9:53 PM
  • Hi,

    Have you tried "CHECKSUM (Transact-SQL)" (https://msdn.microsoft.com/en-us/library/ms189788.aspx) or "HASHBYTES (Transact-SQL)" (https://msdn.microsoft.com/en-us/library/ms174415.aspx)?


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Monday, March 20, 2017 10:13 PM
  • Another approach:

    Convert depdt to an integer with DATEDIFF(DAY, '1900-01-01', depdt).

    Figure out how many bytes each of the four fields takes.

    Assign each of the four fields unique binary columns in the bigint bit-field.

    Multiply each of the four fields varbinary value by the offset number to get it to its columns in the bigint.

    Then add them together.  That will be unique.


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Monday, March 20, 2017 10:19 PM
  • To do this you would need to take trucknbr and DATEDIFF(DAY, '2000-01-01', depdt) and cast both as smallint.

    Also, build a support table that encodes all your Shiporig/Shipdest abbreviations to smallint values.

    Then you would have 4 smallint numbers, that at 2 bytes each, would fit in the 8 byte bigint.


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Monday, March 20, 2017 10:47 PM
  • It is very difficult to assist with that question with good knowledge about the values in these columns. Even with that knowledge it can be difficult to generate a value that cannot collide with something else.

    JediSQL suggested checksum, and as checksum is only a 32-bit value with a quite simple algorithm, you are bound to get duplicates.

    Jedi also suggested HASHBYTES and that is a good choice.

    • Proposed as answer by Teige Gao Wednesday, March 29, 2017 9:51 AM
    Monday, March 20, 2017 10:52 PM
  • What's wrong with a GUID?  Or an identity?  Or a sequence?  Or your current PK?

    Josh

    Tuesday, March 21, 2017 3:27 AM
  • I guess that you are really wanting to concat the string values not numbers? Do this with varchar then convert to a number if you really want to.
    Tuesday, March 21, 2017 6:03 AM