Ltrim and Rtrim not working in where condition

1

Hello Sir,

I'm using LTRIM AND RTRIM function to compare the name between two tables. But the trim function is failed to compare. I already use char function but also not working.

Please help me

2

Please provide sample data?

create table #yaman(id int, name nvarchar(50) )

insert into #yaman
select 1, 'Hello'

etc

3

insert into table(Name1) values ( 'GRUPOS ')

Insert into table1(Name2) values ('GRUPOS ')

And I am using

select REPLACE(REPLACE(REPLACE(Name1, '!', ''), '#', ''), '$', ''), LTRIM(RTRIM(dbo.trim(name2))) -- Result is 'GRUPOS ' and 'GRUPOS ' and also lenght of both is different 8 , 9

4

did you test the script you posted on your computer with the sample data you provided?

5

yes I tested, Actually I found that there is where space after word that is L and R trim is not able to compare it.

6

this does not work on our computers

Insert into table(Name1) values ( 'GRUPOS ')

Insert into table1(Name2) values ('GRUPOS ')


select REPLACE(REPLACE(REPLACE(Name1, '!', ''), '#', ''), '$', ''), 
LTRIM(RTRIM(dbo.trim(name2))) --

``
7

This is actual word in source 'GRUPOS ' and when I run below query

select Distinct REPLACE(REPLACE(REPLACE(c_cve_cxc, '!', ''), '#', ''), '$', ''), c_cve_cxc, LTRIM(RTRIM(dbo.trim(c_cve_cxc))) From table1

I got that result : Result is 'GRUPOS ' and 'GRUPOS ' and also lenght of both is different 8 , 9

8

This is Trim function using in query :
ALTER FUNCTION [dbo].[TRIM](@String VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,CHAR(10),''),CHAR(13),''),char(9),''),CHAR(32),''),'][',''),'',CHAR(32))));
END

9

Please help

10

please provide clean data in the form of . help us help you

create table xyz

insert into xyz
11

Create table table1 ( name 1 char(100) )
Insert into table1 values ('GRUPOS ')

Create table table2 ( name 1 char(100) )
Insert into table2 values ('GRUPOS ')

both are remote table, I am not sure about data type of name1 and name2. It is just I assumned.

12

The title of this thread suggests that you've having problems in a WHERE clause but none of the examples you've provide has a WHERE clause. Please post the actually code that your having difficulty with. Also, what do you mean by "both are a remote table". In relation to the database you're working from, where are these two tables are are they also on an SQL Server?

13

okay Sir,

  1. Yes it is sql server.
  2. Here is my code :

select * from
OPENQUERY(abc, 'SELECT c_cve_cxc,agencia from DB.dbo.table1,DB.dbo.table2
WHERE LTRIM(RTRIM(c_cve_cxc)) = LTRIM(RTRIM(agencia)) and fec_vta >=20190101 and fec_vta <=20191231')

14

okay Sir,

  1. Yes it is sql server.
  2. Here is my code :

select * from
OPENQUERY(abc, 'SELECT c_cve_cxc,agencia from DB.dbo.table1,DB.dbo.table2
WHERE LTRIM(RTRIM(c_cve_cxc)) = LTRIM(RTRIM(agencia)) and fec_vta >=20190101 and fec_vta <=20191231')

15

Is that information is okay for you?

16

What is the source system you are querying? Is it another SQL Server - or is it some other product? The data types of the source system columns will matter - depending on the product and how that product implements those data types.

In some systems - trailing blanks are considered and others they are not - depends on the system and collation used.

As in your other thread - it appears to be an issue with non-printable characters in the data.

17

Thanks for your reply.

Both are sql server source and destination. And we don't know about data type of Source table column.

we cannot developed user defined function that can trim any type of trialing spaces ?

18

In SQL Server, LTRIM and RTRIM remove spaces (char(32) only). It looks like you have a char other than space in the data, could be tab (char(9)), line feed (char(10)), carriage return (char(13)) or something else. You need to check for non-space, non-letter, non-number chars in the data.

On a higher level, if data needs to be LTRIMmed that should be done once as the data is being loaded. The best method to do that is an INSERT trigger, so that it cannot be missed during the load.

RTRIM is not necessary for char comparison in SQL Server. 'GRUPOS' followed by no spaces is equal to 'GROPOS' + SPACE(100) in SQL Server. That is, SQL Server itself will adjust for trailing spaces, as in fact most relational dbms' do.

19

Once again, you've included dates without encapsulating single quotes and that may be a part of the problem. What is the datatype of the fec_vta column?