ASCII issues in SQL Server table export

Question

I recently had an issue with an SSIS package where it failed to export the data in a SQL table. I was able to narrow down the problem to a record which contained a strange character in one field. The field was CUST_NAME and the value in it was:

GUY & O�NEILL, INC.

Of course, the question mark inside the diamond should have been an apostrophe.

Is there any way to track these sort of strange characters using a query? I'd want to search the whole table, but for all "out of the ordinary" characters. I have no idea where to even start.

@Larnu - Basically, anything you wouldn't expect. Like that weird diamond. I don't know what other weird characters might show up, because I definitely wasn't expecting that one. — Johnny Bones
– Johnny Bones, Commented Jan 9, 2023 at 17:50
That could be anything. The above looks like a name, for example, so I wouldn't expect a number. So would LIKE '%[^A-z ]%' work? That will, of course, also flag due to the comma and the period. — Thom A
– Thom A ♦, Commented Jan 9, 2023 at 17:51
@larnu - Well, I'd expect numbers, some punctuation (i.e. "O'Neill" or "Catch 21") would be within the realm of a customer name, but definitely not a square or a diamond. I'm trying to think what other symbols I've seen that aren't within the normal ASCII values. Basically, anything that might trip up an SSIS package trying to export data to a CSV file. — Johnny Bones
– Johnny Bones, Commented Jan 9, 2023 at 17:54
Such a character wouldn't "trip up" an SSIS package though. SSIS supports nvarchar data using the WSTR datatype. — Thom A
– Thom A ♦, Commented Jan 9, 2023 at 17:56

Johnny Bones · Accepted Answer · 2023-01-09 18:23:06Z

0

I just found some code that does what I need. For anyone who might have a similar issue, I used this code:

SELECT
    CASE_NUMBER,
    ACCOUNT_FULL_NAME,
    patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ACCOUNT_FULL_NAME) as [Position],
    substring(ACCOUNT_FULL_NAME,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ACCOUNT_FULL_NAME),1) as [InvalidCharacter],
    ascii(substring(ACCOUNT_FULL_NAME,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ACCOUNT_FULL_NAME),1)) as [ASCIICode]
FROM [dbo].[CLNT_SVC_GOLD_SURVEY_EMAILS_WEEKLY]
WHERE patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ACCOUNT_FULL_NAME) >0

It provided me with the following output:

CASE_NUMBER ACCOUNT_FULL_NAME       Position    InvalidCharacter    ASCIICode
02884401    GUY & O�NEILL, INC.        8        �                     63

answered Jan 9, 2023 at 18:23

Johnny Bones

8,4967 gold badges61 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ASCII issues in SQL Server table export

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related