I ran into an issue today that I wanted to write about so that maybe I remember the solution. We ran into a case where the source data in a column had an unprintable character. In this case, it was a line feed character, which is ASCII value 10, and they had 7 instances in this one row. “How did that get in there? Surely that’s an edge case and we can just ignore it,” and dear reader, I’ve been around long enough to know that this is likely a systemic situation.
Click through for two ways of answering this against data already in SQL Server, as well as one additional route explained but sans demonstration.