CODEWITHSUNDEEP
sqlsql-server
DG86
DG86

Reputation: 1

Trouble Comparing Values From 2 Tables in SQL

I have a table (Table1) that holds data I brought in from a simple text file. Each row has a RowID, and there are columns for page number and errors that start with null values, then the Data column that holds each row from the text file.

In my test data I know that RowID 2 has a company name in the Data column (roughly centered in the original text file). I need to compare that with Col01 of the Customers table to see if the company name is there. It seems like it should be a simple select, but I get zero rows unless I hard code the value from Table1.

Here is the query I have tried with no luck.

SELECT c.RowID, c.Col01, c.Col02 
FROM Customers c 
WHERE c.Col01 = 
    (SELECT RTRIM(LTRIM(t.Data)) 
     FROM Table1 t 
     WHERE t.RowID = '2')

If I hard code the value from RowID 2 of Table1 I get several matches (with different addresses in Col02 and Col03). So I know there is data in Customers that matches the value in the Data column of Table1 on RowID 2.

The goal is to update the Errors column in Table1 to G for customers that are in the Customers table and B for ones that are not members/customers.

So why does the above select statement return zero rows when there are matches?

I'm using MS SQL 2008. The hard-coded version I tried was

SELECT c.RowID, c.Col01, c.Col02 FROM Customers c WHERE c.Col01 = 'Sunny Donuts'

The value in table1 has 35 or 40 spaces before Sunny and a few dozen after Donuts, so I used the LTRIM and RTRIM to get rid of that.

That returned a few rows. The customers table has these columns:

Col01 is the company name, and col02 through Col04 are for the address, city, state, and so on. Those can all be null values.

Table1 has these columns:

These are both legacy tables (in use for several years). In both tables the RowID is an identity specification with a seed of 1 and an increment of 1.

Upvotes: 0

Views: 227

Answers (2)

DG86
DG86

Reputation: 1

Thanks Noah, you were right.

There were hidden characters (CR and LF) and that prevented the match. After I stripped those off of the data in table1 it solved the problem.

Upvotes: 0

Noah Bridge
Noah Bridge

Reputation: 345

When I have issues with a compound SQL statement, what I usually do is to look at the results returned by portions of the statement. Therefore, I would try to see what this portion returns by itself:

SELECT RTRIM(LTRIM(t.Data)) FROM Table1 t WHERE t.RowID = '2'

If the result looks good, I would then look for the possibility of a sneaky invisible character by testing for the length of the returned string:

SELECT LEN(RTRIM(LTRIM(t.Data))) FROM Table1 t WHERE t.RowID = '2'

If the result of the first query is 'Acme, Inc.', for example, this second query should return 10. If it does not, then there is a character in t.Data that should not be there. Just to make the comparison more visual, I would run this query:

SELECT LEN(RTRIM(LTRIM(t.Data))), LEN('Acme, Inc.'), '"' + RTRIM(LTRIM(t.Data)) + '"'
  FROM Table1 t WHERE t.RowID = '2'

The third value above wraps the string around quotes to better visualise the presence of an invisible character either at the beginning or the end of the string.

If the above does not help, I would then focus on the values in column "RowID" of table "Table1". If that column is numeric, it is better to compare against the number 2, instead of '2', to avoid implicit conversions. If "RowID" is defined as alphanumeric, I would run this query to see if it has invisible characters as well:

SELECT LEN(t.RowID), '"' + t.RowID + '"' FROM Table1 t

I hope the above is of some help.

Upvotes: -1

Related Questions