invalid number error

Table1 has values 12345 and 1234
and table2 has one value 12345,1234

When i try to use the following query: am getting invalid number.
select * from table1 a, table2 b
where a.tablecol1 in (b.tablecol)

any other way to execute this.
sakthikumarAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Try this query

select * from #table1 a
inner join #table2 b on b.ID like '%' + a.ID + '%'

Open in new window

0
 
OP_ZaharinConnect With a Mentor Commented:
- you cannot have a data in a column with comma (,) work for the IN function. you need to save the data as follows in table2:
tablecol
12345
1234

- then try again use the same query to execute:
select * from table1 a, table2 b
where a.tablecol1 in (b.tablecol)
0
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
This query is not advicable, since it may take much more time for a table with very huge data.
I suggest you to store the data - '12345,1234', seperated into 12345 and 1234 and store as numbers (separate rows). By this way, you can take advantage of numeric column's speed on index and your queries could perform faster.
0
 
sakthikumarAuthor Commented:
Thank you, nice suggestion from both of you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.