Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44

    Unanswered: Convert CHAR to INT and vice versa

    So I'm trying to create a unique ID for a record in my database by appending two other IDs from other tables with a letter A-Z at the end. For example:

    LessorId + ProspectCode + Unique ID [A-Z] =

    133 + 590 + A =

    133590A

    The thing is that I need to be able to generate the unique letter automatically in a stored procedure. Does anyone know how I might do this?

    Originally I did this in my ASP.NET code, and I created a CHAR variable with the value 'A', and then I converted it to INT and then I counted the amount of existing records for the current owner and added that to the INT for 'A' and then converted it to CHAR, then appended it to the other two IDs and that worked. I've tried to do it in the sproc, but without luck. This is what I have so far:

    DECLARE @firstChar CHAR
    SET @firstChar = 'A'

    SET @PaymentId = @ProspectCode + CAST(@LessorId AS VARCHAR(MAX)) +
    CAST(CAST(@firstChar AS INT) + @countPaymentId AS VARCHAR(MAX))

    Any suggestions would be greatly appreciated.
    Paul Palubinski

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    You need to use the ASCII() and CHAR() built in functions to respectively:
    a) convert the letter 'A' to a number, and then
    b) convert a number back to a character

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you give us the reason why you want to construct the unique Id to be the way you described?

    How will your query look like if you need all the
    • records of a certain LessorId - ProspectCode combination?
    • records of a certain LessorId?
    • records of a certain ProspectCode combination?
    • join those records with the Lessor and ProspectCode tables?
    How easy will that be? How about performance after a while?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Quote Originally Posted by thompbil View Post
    You need to use the ASCII() and CHAR() built in functions to respectively:
    a) convert the letter 'A' to a number, and then
    b) convert a number back to a character
    That's exactly what I needed. Thanks!
    Paul Palubinski

  5. #5
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Quote Originally Posted by Wim View Post
    Can you give us the reason why you want to construct the unique Id to be the way you described?

    How will your query look like if you need all the
    • records of a certain LessorId - ProspectCode combination?
    • records of a certain LessorId?
    • records of a certain ProspectCode combination?
    • join those records with the Lessor and ProspectCode tables?
    How easy will that be? How about performance after a while?
    The only reason it is that way is because that's what I was told the client wanted. I would much prefer to just use a basic incrementing INT ID, but they specifically want it that way...
    Paul Palubinski

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    To cover all bases, you might consider breaking them out into three separate columns, and joining them together for the front end's use. The users don't need to know everything about the back end, after all. At it may make the queries Wim pointed out easier on you. Not to mention indexable.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I totally agree with MCrowley's post.

    Is it a database/application that you will have to maintain in the future? If so, make your future life easier. You will gain flexibility (normalisation, normal joins: no SUBSTRINGs), reliability (FK constraints, no SUBSTRING(.., X, Y) where you could make a mistake with the values of X and Y), efficiency (usable indexes = speed), ... by presenting your users the interface they asked for, but designing the database according to art and best practices.

    Your user's job is to tell you WHAT they want, not HOW it should be done. That is your responsibility.
    I would much prefer to just use a basic incrementing INT ID
    Just follow your instincts
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Quote Originally Posted by Wim View Post
    I totally agree with MCrowley's post.

    Is it a database/application that you will have to maintain in the future? If so, make your future life easier. You will gain flexibility (normalisation, normal joins: no SUBSTRINGs), reliability (FK constraints, no SUBSTRING(.., X, Y) where you could make a mistake with the values of X and Y), efficiency (usable indexes = speed), ... by presenting your users the interface they asked for, but designing the database according to art and best practices.

    Your user's job is to tell you WHAT they want, not HOW it should be done. That is your responsibility.Just follow your instincts
    Haha, I have taken to heart what you guys have said, and I will look into generating the user requested ID dynamically on the front end. THe only thing is that users might want to search by that ID, so I'll have to distill it into its parts programatically and stick it into a meaningful query for the badk end...
    Paul Palubinski

  9. #9
    Join Date
    Aug 2009
    Posts
    262

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •