April 6, 2009 at 7:55 am
Hi
I have two tables
Table 1
Price[Char(11)] TYPE[Char(1)]
10.333 S
10.3 B
10.33 B
Table 2
Price[Char(11)] TYPE[Char(1)]
10.3330 S
10.30 B
10.333 B
I have to make a join on price column.If the there zero after the decimal point, should not be considered.
eg. 10.333 and 10.3330 are same
The output should be
Price TYPE
10.333 S
10.3 B
how to proceed on this
April 6, 2009 at 8:08 am
I would suggest that you cast both columns to numeric before joining. That way trailing zeros will be completely ignored. This, of course, assumes that all values in the column be castable to numeric.
Why does your expected result have only 1 row with 10.333? If you're just joining on the price (as you said) then there should be 1 row with 10.333 S and one row with 10.333 B. Or do you want to join on both columns?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2009 at 8:12 am
Not sure that I understand the way that you want the join, because the description that you wrote doesn’t match the results that you showed (or at least it didn’t match what I understood that you want). One way of doing it, is to base the join on convert function on both columns to money datatype:
select t1.price, t1.type, t2.type
from Table1 t1 inner join Table2 t2
on convert(money, t1.Price) = convert(money,t2.Price)
Notice that the performance won’t be good for such query. By the way – why are you storing price as string and not use a numeric data type?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 7, 2009 at 10:46 am
You can even pad all the "0" till yuo reach length of the string as 11.
http://www.developerdotstar.com/community/node/319
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply