Get best of the week

Why is a dash becoming a question? Or problems with coding data in SQL

A client contacted me, to whom I six months ago did uploading data from Excel to the product list Applications from the client. Video on the result.
The file was formed in China, where the usual Cyrillic alphabet is not used.

An item with the name “ABM3C ‐ 25 ‐ D ‐ Y ‐ T» T ”is loaded.

After downloading, we get “ABM3C? 25? D4Y? T.” After ProFiler, I caught the write procedure:

exec 3, 'ABM3C-25-D4Y-T'

That is, the replacement is already done on SQL itself. The database was created on the Client Communicator platform. Out of habit, I checked the sorting options for the database itself, everything is fine - Cyrillic_General_CI_AS, as recommended during installation.

Played with cast and convert - issues questions instead of dashes. I couldn’t solve the problem myself ... I contacted the Client Communicator platform developer technical support service with this problem.

The developer’s response at first was very surprising: “You must use the Nvarchar data type, and you probably use Varchar.” Recording is performed in a field with the nvarchar data type (set by default when creating a field in the configurator).

I show that the simplest script gives me all the same unfortunate question marks.
select 'ABM3C ‐ 25 D D4Y ‐ T', cast ('ABM3C 25 25 D D4Y T T' as nvarchar) I

got advice - put N. before the first single quote,

select N'ABM3C ‐ 25 ‐ D4Y ‐ T '

gave me the desired result . By adding N, we pointed out SQL that we need to process this string as a value in Unicode.

Corrected the procedure call by adding N:

exec 3, N'ABM3C ‐ 25 ‐ D4Y ‐ T '

I always knew that I had to put the letter N in front of the quotation mark, but I did not attach any special importance to it until I came across this problem.

More articles:


All Articles