Hello ,
I am fetching data from remote server. and my query is
select * from from table1 , table2
WHERE LTRIM(RTRIM(agency)) = LTRIM(RTRIM(agencyname)) and date>=20190101 and date<=20191231')
But is did not fetched correct result.
I found and check table2 data has some white space characters that is not removed from LTrim and RTRIM function. Please help
Heh... "Must look eye"! The real problem is that your literal dates must be encapsulated in single quotes and you also have a spurious parenthesis at the end of your query.
Your LTRIM/RTRIM thing also makes the query mostly non-SARGable. Is it really necessary?
Sorry no luck , I removed it is still data not fetched correclty. There is only some agencies which is not fetched from remote sql server.
Based on your other question - it appears that either 'agency' or 'agencyname' may contain special characters. In most cases, trailing blanks are not considered when comparing string data - this depends on the data type and padding and/or collation associated with the column/table/database.
LTRIM/RTRIM are usually not needed for this...which leads me to believe there are special characters involved in those fields. If you can identify those special characters - and how they are getting entered - then you can fix the data and your query will work. If you cannot...then you probably need to strip out the special characters before comparing.
If you can copy/paste the actual data that you expect to match - where they are not matching, maybe we can help identify the problematic characters.
Below is example one agency of two tables : Only the second one trim properly .
'GRUPOS '
'GRUPOS '
If you don't want non-display chars in the data, remove them as part of the INSERT, don't re-correct the data every time you do a repair. Instead, use an INSERT trigger to correct the data once as it first INSERTed. If you truly require it, you can keep a separate column that gives you the pattern of the original data, i.e. the original data was ' ~LR', i.e., the data was preceded by a space and followed by a Line feed char and Carriage return char.
And you added the single quotes to the dates like I said?
Thanks, But can we have use any user defined function to to remove that 'LR' etc. ?
Yes... might as well get rid of TABs, while we're at it...
REPLACE(REPLACE(REPLACE(SomeString,CHAR(9),''),CHAR(10),''),CHAR(13),'')
To Scott's very good point, though, if you use such a formula in a WHERE clause instead of permanently removing such control characters from the data during the original INSERT of the data, your code will be permanently slow and resource intensive. You NEED to fix the underlying data to improve the data quality to what it should be.
I understand, I am following them to do so. Thank you
And I applied above code but still no success. Query fetching all records except those agency
select * from from table1 , table2
WHERE
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(c_cve_cxc,CHAR(9),' '),CHAR(10),' '),CHAR(13),' ') )) = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(agencia,CHAR(9),' '),CHAR(10),' '),CHAR(13),' ') ))
and date>=20190101 and date<=20191231
We cant know because we are not there sitting with you and it is trial and error. And those usually do not work well.
Display the ascii values in the known "bad" data/bytes, something like as given below. Then any ascii values that are not good chars, REPLACE them too. For example, if somehow 255 showed up in your data, you would add a " REPLACE(..., CHAR(255), '') " to your other REPLACEs in the code.
SELECT
agency,
ASCII(SUBSTRING(agency, 4, 1)),
ASCII(SUBSTRING(agency, 5, 1)),
ASCII(SUBSTRING(agency, 6, 1)),
ASCII(SUBSTRING(agency, 7, 1)),
ASCII(SUBSTRING(agency, 8, 1))
FROM dbo.your_tableWhat is the data type for your DATE column in that table?
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.