Nested SQL Script using in Not Returning any data

I have 1 SQL Table and 1 SQL View as follows:

Table1 contains a column VendorId along with several other columns. The VendorId column in Table1 is formatted as varchar(255) and is typically storing a VendorId that consists of Alpha and Numeric values up to about 8 characters total. Ex. ATT0000, CEDA0678, SPIR0193, SPIR048, SPIR1433. There are several other Vendors in Table1 with data, etc.

View1 contains some of the Vendors above. My view is built using a Case Statement which parses the Vendor Id out of some text and I'm only returning the VendorId in View1.

When I attempt to return all Records from Table1 as:

Select * From Table1 Where VendorId in (Select VendorId from View1)

I get no results. however if I Select VendorId from View1 it returns the Vendors: ATT0000, CEDA0678, SPIR0193, SPIR048, SPIR1433. Also if I Select * from Table1 Where Vendorid in ('ATT0000', 'CEDA0678', 'SPIR0193', 'SPIR048', 'SPIR1433') I get results but the "in" does not work in the above script as I get no results. I've also tried wrapping ltrim(rtrim(VendorId)) around my Select statement with no results.

This returns values:

This returns values:

This returns nothing:
Select *
From _DSI_GobyPayableTransaction
Where ltrim(rtrim(GPVendorId)) in (Select ltrim(rtrim(GPInvalidRecord)) From _DSI_GobyErrorMessages A Where GPErrorMessage like '%Vendor%')


I use 'in' all the time to return records but now I'm getting nothing. Ideas? Suggestions? This shouldn't be this complicated.

Is it possible you have unprintable characters (carriage return, linefeed etc) in the version coming out of your view? If you try:

SELECT GPInvalidRecord, LEN(GPInvalidRecord) FROM _DSI_GobyErrorMessages

do the lengths look correct?

there is an extra space at the End of the column in the view so even when I use ltrim(rtrim(GPInvalidRecord)) it shows as 8 or 9 yet the number of characters visible only show 7 or 8. I thought ltrim(rtrim(column)) would strip out any extra space or as you point out a possible carriage return, line feed. how do I get rid of that?


I would normally just do a something like:
left(ltrim(rtrim(columnname)), len(columnname)-1) to get the cleaned up version of that column but as you can see in the result set sometimes the len is off by 1 and other times its off by 2.

Ideas/suggestions? Is there a charindex to find a carriage return or some value that I don't see?

You need to identify what those extra characters do that use the ASCII function: ASCII(RIGHT(column, 1))

Once you have identified the bad characters - use a replace to get rid of them:

REPLACE(column, char(13), '') --removes carriage return
REPLACE(REPLACE(column, char(13), ''), char(10), '') --remove carriage return and line feed


just stumbled across that same syntax, updated my view and am good to go.

Thanks for the help!