• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1110
  • Last Modified:

SQL - convert int to string

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?
4 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
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.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
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.
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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.
Scott PletcherSenior DBACommented:
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).
tommym121Author Commented:

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now