469,903 Members | 1,499 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

Concatenation problem: two integers and a char

2
Hello to all,

I am writing a query that is attempting to take three fields in a table, and create a new field called "MyKey." I'm doing this using concatenation. The problem: two of these fields, Storage_Facility and Storage_Receipt_Number, are integer fields. The third of these fields, Receipt_Suffix, is a char field. It appears that SQL server will not allow this. I can do it in Microsoft Access, why not in SQL Server? Is there any way around this?

Here's the relevant part of my query:

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(MyTable.STORAGE_FACILITY+MyTable.STORAGE_RECEIPT_ NUMBER+MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,

Etc…

If you can help, great!

Thanks.
Jul 19 '07 #1
1 1451
vijaii
15
Hello to all,

I am writing a query that is attempting to take three fields in a table, and create a new field called "MyKey." I'm doing this using concatenation. The problem: two of these fields, Storage_Facility and Storage_Receipt_Number, are integer fields. The third of these fields, Receipt_Suffix, is a char field. It appears that SQL server will not allow this. I can do it in Microsoft Access, why not in SQL Server? Is there any way around this?

Here's the relevant part of my query:

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(MyTable.STORAGE_FACILITY+MyTable.STORAGE_RECEIPT_ NUMBER+MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,

Etc…

If you can help, great!

Thanks.

Try any of these....

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(CAST(MyTable.STORAGE_FACILITY AS VARCHAR(10))
+ CAST(MyTable.STORAGE_RECEIPT_ NUMBER AS VARCHAR(10))+
MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,


SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(CONVERT(VARCHAR(10),MyTable.STORAGE_FACILITY) +
CONVERT(VARCHAR(10),MyTable.STORAGE_RECEIPT_ NUMBER)+
MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey ,
Jul 20 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Oliver Crow | last post: by
18 posts views Thread by Larry Menard | last post: by
13 posts views Thread by Nicholas | last post: by
24 posts views Thread by Caroline | last post: by
35 posts views Thread by michael.casey | last post: by
34 posts views Thread by Larry Hastings | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.