Solved

# SQL - convert int to string

Posted on 2012-09-14
913 Views
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?
0
Question by:tommym121

LVL 65

Expert Comment

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.
0

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.
0

LVL 65

Assisted Solution

>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))
GO

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

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.
0

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).
0

Author Closing Comment

Thanks
0

## Featured Post

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 (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. 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…