CODEWITHSUNDEEP
sql
nina_dev
nina_dev

Reputation: 695

Removing spaces and compare two strings

I want to compare 2 tables and see if the value of one of their columns is identical. Here is the code that wrote:

    select T1.TaskCMPLTDetails,T2.TaskCMPLTDetails, T1.RequestID,T2.RequestID, t1.ProtocolID,t2.ProtocolID, t1.BuildCMPLTDT, t2.BuildCMPLTDT
    From table as T1 inner join table as T2
    on  T1.ProtocolID  = T2.ProtocolID 
    where REPLACE(LTRIM(RTRIM(T1.TaskCMPLTDetails)), '  ', ' ') <> REPLACE(LTRIM(RTRIM(T2.TaskCMPLTDetails)), '  ', ' ') 
    and t1.BuildCMPLTDT=t2.BuildCMPLTDT

And, here is the results that I got back; (copied only a row):

Y,Val,2017,1158,    Y, Val, 2017, 1158,   2017  2017    1158    1158    1900-01-01  1900-01-01

As you can see in the result, TaskCMPLTDetails is the same for the both tables, why do I see this? The code supposed to return the results that donot have an identical TaskCMPLTDetails!!

Upvotes: 0

Views: 819

Answers (2)

forpas
forpas

Reputation: 164069

With this statement:

REPLACE(LTRIM(RTRIM(T1.TaskCMPLTDetails)), '  ', ' ')

you replace every 2 adjacent spaces with 1 space.
So when your column's value has 3 spaces in it like this:

'A   B' (3 spaces)

the result will be:

'A  B'  (2 spaces)

because in the initial value only 2 adjacent spaces were replaced by 1 space,
so this result contains also 2 adjacent spaces.
The REPLACE function acts only once and not recursively so to replace again and again until there are not any 2 adjacent spaces.
The solution to your problem would be to remove all spaces from both columns before comparing them, because the columns contain comma separated values so there will be no unwanted joining of values in this case (or so it seems from the sample data you posted):

where REPLACE(T1.TaskCMPLTDetails, ' ', '') <> REPLACE(T2.TaskCMPLTDetails, ' ', '')

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a simple trim() TRIM(T1.TaskCMPLTDetails) <> TRIM(T2.TaskCMPLTDetails)

    select T1.TaskCMPLTDetails
      ,T2.TaskCMPLTDetails
      , T1.RequestID
      ,T2.RequestID
      , t1.ProtocolID
      ,t2.ProtocolID
      , t1.BuildCMPLTDT
      , t2.BuildCMPLTDT
    From table as T1 
    inner join table as T2  on  T1.ProtocolID  = T2.ProtocolID 
    where  TRIM(T1.TaskCMPLTDetails) <> TRIM(T2.TaskCMPLTDetails)
    and t1.BuildCMPLTDT=t2.BuildCMPLTDT

Upvotes: 0

Related Questions