0

I'm comparing values from two different database. In one table the name is stored with dots(i.e A.B C) and in another table the same value is stored as AB C(i.e in second table none of the names contain dots) In SQL how can I compare the values? Dot can come anywhere in the name.

Manu
  • 191
  • 1
  • 4
  • 12
  • 2
    What RDBMS are you using? – Mureinik Feb 26 '15 at 07:03
  • you means to say you have table1 in database1 and table2 in database2 and you want to compare the values from both the tables? – Murtaza Feb 26 '15 at 07:04
  • table 1 column = 'ABC', I have to retrieve data from table 2 based on value from table 1. ie select * from table2 where column = 'ABC' . But in table 2 value is A.B.C. thats y not retrieving data – Manu Feb 26 '15 at 07:07
  • @Manu: As Mureinik asked: what RDBMS are you using (SQL Server, mysql,..) ? You have to answer that first. – Raging Bull Feb 26 '15 at 07:08
  • @Manu: So, those tables are in different databases, right? – Raging Bull Feb 26 '15 at 07:12
  • @Manu: alright ! I have updated my answer. You can check it now. See the link also to connect those databases. – Raging Bull Feb 26 '15 at 07:14

2 Answers2

0

Use replace:

column1 = replace(column2,'.','');
Jens
  • 67,715
  • 15
  • 98
  • 113
0

If you are comparing columns from two tables which are from different databases, you have to find a way to connect those two databases.

In SQL Server, you can use Linked Servers.
For more details about linked server, See this link.

After that you can compare them using REPLACE:

WHERE REPLACE(T1.ColName,'.','')=T2.ColName

For example:

SELECT T1.ColName,T2.ColName2,
       CASE WHEN REPLACE(T1.ColName,'.','') = T2.ColName THEN 'Equal' ELSE 'Not Equal' END AS EqualOrNot
FROM Table1 T1 JOIN
     LinkedServerName.DBname.dbo.Table2 T2 ON T1.fk=T2.pk
Community
  • 1
  • 1
Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • Sir the tables are in two different databases, i think your query will work within the single database.. but for multiple database he/she will either need to have linked server or need to check it through different approach like SSIS or some other way. – Murtaza Feb 26 '15 at 07:05
  • Thanks @RagingBull. But I have the values from Table1 and passing as a parameter to fetch the details from Table2. Due to the special character in Table2, I'm not getting the results. – Manu Feb 26 '15 at 07:18
  • Let me try the query u send @RagingBull.. will update the result. – Manu Feb 26 '15 at 07:20
  • Thank @RagingBull!! Working!! :) – Manu Feb 26 '15 at 07:41