HELP!! CONCATENTATE 2 Integers without doing math in SQL 2000

Last Post 01 Jul 2004 03:01 AM by Ado_SQL. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sportsphotog
New Member
New Member

--
30 Jun 2004 03:24 PM
I am looking to do a concatentation of 2 integer columns without converting them to varchar in SQL Server 2000. Does anyone know how to do this without doing a cast or convert statement? Obviously when using the concatenation operator(+) with two integer columns will produce math instead of a cobined string. I am asking due to performance issues and need to leave the fields as INT's. I know the problem is in my from statement but I am uncertain how to clean it up to function faster. I don't nescessarily need to the columns to be int's but I need the from statement working more efficiently
Here is the statement that I am trying to change:

select
cast(a.so_id as varchar ) + cast(a.line as varchar) cust_db_shipment_key,
MULTIPLE OTHER PARTS OF THE STATEMENT

from SO_LINE a left join SO b on a.SO_ID = b.SO_ID where a.carrier_id = '2' and cast(a.so_id as varchar) + cast(a.line as varchar) = ?

Thanks for the help in advance!!!!!!!!
Steve

Ado_SQL
New Member
New Member

--
01 Jul 2004 03:01 AM
Perhaps I am not understanding what you are doing I use:

select ltrim(str(N1)) +ltrim(str(N2)) as Number from <table>

which seems to work fine, even if I add a bit of maths in the query too!

HTH
ScottPletcher
New Member
New Member

--
02 Jul 2004 06:02 AM
If possible you should do the WHERE comparison on the original columns, not the concatenated column.

WHERE a.carrier_id = '2' and so_id = intValue AND a.line = intValue

That will allow index(es) on a.so_id and/or a.line to be used if available. If you concatenate them prior to the comparison, SQL will likely not consider an index at all.
You are not authorized to post a reply.

Acceptable Use Policy