Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

int and varchar joined

Posted on 2011-03-17
4
Medium Priority
?
405 Views
Last Modified: 2012-05-11
join an int and varchar column

but the column that is varchar has words sometimes
I do not want that joined

0
Comment
Question by:rgb192
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 1000 total points
ID: 35160427

select *
from tableA
inner join TableB on ( (Cast(TableB.Varhcharfield as Integer) = TableA.IntegerField)
                                  and (ISNUMERIC(TableB.Varhcharfield) = 1))
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 35160714
ewangoya, converting to int will fail if the asker has , or . in the data. check this example by converting to INT.
I prefer converting to MONEY in such cases.
declare @table1 table(col1 int)
declare @table2 table(col2 varchar(10))
insert @table1 values(10),(20)
insert @table2 values ('10'),('23.00'),('123,234'),('invalid')
select *
  from @table1 t1
  join @table2 t2 
    on t1.col1 = convert(money,t2.col2)
 where ISNUMERIC(t2.col2) = 1

Open in new window

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35161201

True, we acquire more knowledge each day.
0
 

Author Closing Comment

by:rgb192
ID: 35165819
thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question