SQL server identitical strings not matching SQL server 2014

I have 2 tables : In table 1 we insert data from the db normally , the second we use a text file and bulk insert.
I have one common string (used for join in the two tables)
in case they both shows a string with special character like Maintenance, Repair & Overhead with the same len (41)
 when I try to join them , they are not joined anf returns no results
In case it is a word with no special characters such as   Agile methodologies works fine ...
\Please help me in this
assaadrezkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
define "insert data from the db normally"

if the date in the other db is picked-up from file...

so, what type of file is it? e.g. xml, csv
and what encoding is used?
what operating system does the file reside in?
are there any other operations on the file? e.g. secure FTP

also; are the columns the same data type and is the collation the same?
G GodwinDatabase AdministratorCommented:
One of your tables is likely getting non-printable special characters.  

You may need to dig through records that are not joining as expected.  

Here's an example of what I would do to see if that is the problem.

create table #TAB1 (id int identity, col1 varchar(20))

create table #TAB2 (id int identity, col1 varchar(20))

insert into #TAB1 (col1) values 
  ('abcdefG')
, ('abcdefH')
, ('abcdefI')
, ('abcdefJ')



insert into #TAB2 (col1) values 
  ('abc'+ char(28) +'defG')   -- added non printable character. (Act like you don't know it's there.)
, ('abcdefH')
, ('abcdefI')
, ('abcdefJ')


select * from #TAB1
select * from #TAB2

--See which ones look like they should join but don't.
--See if any of the lengths don't look right too. 
select t1.*, len(t1.col1) Len1, t2.*, len(t2.col1) Len2
from #TAB1 t1
full outer join #TAB2 t2 on t1.col1 = t2.col1

1	abcdefG	7	
2	abcdefH	7	2	abcdefH	7
3	abcdefI	7	3	abcdefI	7
4	abcdefJ	7	4	abcdefJ	7
			1	abcdefG	8

-- Inspect these one by one to find the problems.
select substring(col1,1,1), ascii(substring(col1,1,1)) from #TAB2 where id = 1
select substring(col1,2,1), ascii(substring(col1,2,1)) from #TAB2 where id = 1
select substring(col1,3,1), ascii(substring(col1,3,1)) from #TAB2 where id = 1
select substring(col1,4,1), ascii(substring(col1,4,1)) from #TAB2 where id = 1
select substring(col1,5,1), ascii(substring(col1,5,1)) from #TAB2 where id = 1
select substring(col1,6,1), ascii(substring(col1,6,1)) from #TAB2 where id = 1
select substring(col1,7,1), ascii(substring(col1,7,1)) from #TAB2 where id = 1

-- replace the offending character(s)
update #TAB2 set col1 = replace(col1, char(28), '') where id = 1 

-- Try again 
select t1.*, len(t1.col1) Len1, t2.*, len(t2.col1) Len2
from #TAB1 t1
full outer join #TAB2 t2 on t1.col1 = t2.col1

1	abcdefG	7	1	abcdefG	7
2	abcdefH	7	2	abcdefH	7
3	abcdefI	7	3	abcdefI	7
4	abcdefJ	7	4	abcdefJ	7

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
assaadrezk, do you still need help with this question?
G GodwinDatabase AdministratorCommented:
assaadrezk,

Did you have an opportunity to investigate as suggested here?

-gg
assaadrezkAuthor Commented:
thx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.