May 12, 2019 at 5:39 pm
Ok. I don't have your "v_Manufac" view to test against but give this a try.
--===== Note that the following characters are replaced with an empty string if present in the file.
-- NCHAR(9) = TAB character
-- NCHAR(10) = LINEFEED or NEWLINE character
-- NCHAR(13) = CARRIAGE RETURN character
SELECT v.ModelName0
FROM v_Manufac v
WHERE v.ModelName0 NOT IN
(
SELECT ModelNameExclude = s.value
FROM OPENROWSET(BULK 'c:\temp\SampleUnicode.txt', SINGLE_nCLOB) x
CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(x.BulkColumn,NCHAR(9),N''),NCHAR(10),N''),NCHAR(13),N''), ',') s
)
ORDER BY v.ModelName0
;
The "spaces" you were running across aren't spaces at all... they're embedded line terminators, which the REPLACES take out of the picture.
The sub-select in the NOT IN can be executed separately if you want to see whats happening.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2019 at 8:29 pm
Hi Jeff, It works great! I did some initial testing and two thumbs up.
I will test further and if any issues pop up, I will post it.
Thanks again
May 13, 2019 at 3:29 pm
As some of the techs I work with would say, "Cool beans". Thanks for the feedback on this.
As a bit of a sidebar, this is yet another proof that correct identification of the problem is 90% of the solution. Thank you for weathering all the questions that I asked.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy