To be able to help - you need to copy/paste the data from SQL Server, preserving the actual characters in the column.
You can 'brute' force the solution by using a function to clean the string so only valid characters are returned for that column. Something like:
With t1
As (
Select *
, t1Agencia = cs.outputString
From table1 t1
Cross Apply dbo.fnCleanString(t1.c_cve_cxc, '[0-9a-zA-Z') cs
Where t1.[Date] >= '20190101'
And t1.[Date] < '20200101'
)
, t2
As (
Select *
, t2Agencia = cs.outputString
From table2 t2
Cross Apply dbo.fnCleanString(t2.agencia, '[0-9a-zA-Z') cs
Where t2.[Date] >= '20190101'
And t2.[Date] < '20200101'
)
Select *
From t1
Inner Join t2 On t2.t2Agencia = t1.t1Agencia;
Not sure on the WHERE clause - since there is no way to determine which table the [Date] column is coming from...
Here is an example of the function to clean the string:
Create Function dbo.fnCleanString (
@inputString varchar(8000)
, @stringPattern varchar(50) = '[a-Z]'
)
Returns Table
With schemabinding
As
Return
With t(n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
, (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, iTally (n)
As (
Select Top (len(@inputString))
checksum(row_number() over(Order By @@spid))
From t t1, t t2, t t3
)
Select v.inputString
, outputString = (Select substring(v.inputString, it.n, 1)
From iTally it
Where substring(v.inputString, it.n, 1) Like @stringPattern
For xml Path(''), Type).value('.', 'varchar(8000)')
From (Values (@inputString)) As v(inputString);
Go
This may need to be extended to include a collation...it can also be improved for performance if you know that you will always be removing all 'special' characters including spaces.