SQL - convert int to string

Posted on 2012-09-14
Last Modified: 2012-09-14
I have a query that will retrieve two columns of integer,  They are the primary key. I will like to combine them into a unique name.  

e.g.  Key1 and Key2 are my keys

Select a.Key1, a.Key2,  a.Key1+ '-'+a.Key2 from Mytable a

e.g. if key1 = 1010 and key2 = 2002 then the unique name I like to get is '1010-2002'

How do I do that?
Question by:tommym121
    LVL 65

    Expert Comment

    by:Jim Horn
    SELECT CAST(a.Key1 as char(4)) + '-' + CAST(a.Key2 as char(4)) as column_name
    FROM MyTable a

    If the values could be more than four characters than change the 4 to something higher

    You could also define a calculated column in your table that does the above for you.
    LVL 16

    Accepted Solution

    Select a.Key1, a.Key2,  (cast(a.Key1 as varchar) + '-' + cast(a.Key2 as varchar)) as 'UniqueName' from Mytable a

    will do that.

    However, if you want to retain the name in the table you'll have to create a new column for it - it won't go into either of the two fields you're getting the bits from, as these are both int fields.
    LVL 65

    Assisted Solution

    by:Jim Horn
    >I will like to combine them into a unique name.  
    This assumes that Key1 and Key2 are both unique, as performing this calculation itself will not guarantee uniqueness.

    -- Here's how you add this as a computed column to your table
    ALTER TABLE dbo.MyTable
    ADD column_name as CAST(a.Key1 as char(4)) + '-' + CAST(a.Key2 as char(4))

    Then, if you're going to query off it, you can index it.
    LVL 26

    Assisted Solution

    You don't have to specify the size:

    SELECT key1, key2, CAST(key1 as varchar)+'-'+CAST(key2 as varchar) FROM MyTable

    If you want the output to be of speciffic length then you need to make sure that the smaller numbers are still turned into a, let's say, 5 characters string:

    SELECT key1, key2, RIGHT('00000'+CAST(key1 as varchar),5)+'-'+RIGHT('00000'+CAST(key2 as varchar),5) FROM MyTable

    Of course if your numbers get more than 5 digits you will have to increase accordingly the number of '0' you add in front and the number of characters you use in RIGHT:

    -- for 6 digits
    SELECT RIGHT('000000'+CAST(key1 as varchar),6)

    -- for 7 digits
    SELECT RIGHT('0000000'+CAST(key1 as varchar),7)

    and so on.
    LVL 68

    Assisted Solution

    Explicitly specify the length in the CAST, don't rely on the default (which is a length of 30 in this specific situation (for other tasks the default may be different), but that's obscure and most people won't know that).

    Author Closing Comment


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    745 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

    13 Experts available now in Live!

    Get 1:1 Help Now