Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to create a repeatable +ve integer from a nvarchar? Expand / Collapse
Author
Message
Posted Thursday, June 6, 2013 3:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:41 PM
Points: 4, Visits: 16
Hi,

I need to create an id from a nvarchar value that is repeatable, sort of like a checksum or a hash, except that it needs to be unique and positive. Does anyone know how I might do this? I've been scouring the net with no results yet...

To be clear I need to have some function that will turn '00sim384' (and about 3mm other alphanumeric values) into an integer. It also needs to produce the same integer value each and every time.

Before you ask Why? The reason is that we aren't yet storing unique ids for these values yet, so I'm trying to similate a unique integer for each of the values until we can load these into a table and just use the index. Sux to have to do this, but I've got all this generic processing that relies on having this integer id..


Thanks!
Post #1460906
Posted Thursday, June 6, 2013 4:49 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:54 AM
Points: 1,778, Visits: 5,729
If a BIGINT is ok, you could consider this:


convert(bigint,convert(varbinary,'00sim384'))

BUT this will only work for strings up to a maximum of eight bytes in length so if you really have those values as nvarchar and are using unicode characters, you can't use this.

If you don't actually have any unicode in there and can convert to varchar first you can...

convert(bigint,convert(varbinary,convert(varchar,'00sim384')))

Why do you need to be able to repeat the conversion?


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1460925
    Posted Friday, June 7, 2013 7:41 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Yesterday @ 8:14 PM
    Points: 35,215, Visits: 31,665
    Why not just grab the unique values and put then into a lookup table with an IDENTITY or BIGINT column?

    --Jeff Moden
    "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1461081
    Posted Tuesday, June 11, 2013 11:23 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 6:16 AM
    Points: 5,078, Visits: 11,856
    sarah.cook (6/6/2013)
    Hi,

    I need to create an id from a nvarchar value that is repeatable, sort of like a checksum or a hash, except that it needs to be unique and positive. Does anyone know how I might do this? I've been scouring the net with no results yet...

    To be clear I need to have some function that will turn '00sim384' (and about 3mm other alphanumeric values) into an integer. It also needs to produce the same integer value each and every time.

    Before you ask Why? The reason is that we aren't yet storing unique ids for these values yet, so I'm trying to similate a unique integer for each of the values until we can load these into a table and just use the index. Sux to have to do this, but I've got all this generic processing that relies on having this integer id..


    Thanks!


    The max value of an INT is 2,147,483,647.

    Let's say that your varchar column can contain only numbers and letters (upper and lower case). That's 10 + 26 + 26 = 62 options per character position.

    I did some quick maths:

    String Length Number of combinations
    1 62
    2 3,844
    3 238,328
    4 14,776,336
    5 916,132,832
    6 56,800,235,584
    7 3,521,614,606,208
    8 218,340,105,584,896
    9 13,537,086,546,263,600
    10 839,299,365,868,340,000

    I think you can see the problem here - you run out of room for all the different possible combinations if your maximum string length is > 5.

    And if you use a BIGINT (max 9,223,372,036,854,775,807) you can squeeze in only another 5 characters or so.





    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1462453
    Posted Thursday, August 22, 2013 2:15 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Friday, August 29, 2014 8:41 PM
    Points: 4, Visits: 16
    Thanks for all the replies. We did end up converting to a BIGINT, and then just using a numeric conversion. (the values did remain very small, and since it's only a temporary solution we coudl work out that there was small risk of running out of room)

    We had to have this repeatable since we wanted to do this in two different places and then match the values in the ETL.

    We just came across this in another area and have used a ranking method to resolve it (very similar to the proposal to store them in a temporary id).

    Very much appreciated
    Post #1487511
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse