2

I have a query inserting a special character into a table, for instance:

INSERT drinks VALUES( 'Café' )

I would like to do the same thing, in the most portable way possible, using only ASCII in my query, for instance:

INSERT drinks VALUES( 'Caf' + <something> )

Is it possible?

2
  • 2
    Can you explain what the real problem is? In other words, what are you solving by using some ASCII equivalent? Maybe we help can fix that problem so you don't have to use some cryptic kludge. – Aaron Bertrand Mar 20 '17 at 15:43
  • Well, curiosity is certainly a factor. Sometimes, when I send queries to a counterpart to perform database changes, the text file is opened and edited on different machines, and the special characters are damaged. I thought it would be nice to be able to write a portable query to do this. – carlo.borreo Mar 20 '17 at 15:53
3

Use the ASCII representation:

select 
    ascii('Ç'), -- 199
    ascii('é'), -- 233
    ascii('â'), -- 226
    ascii('ê'), -- 234
    ascii('î'), -- 238
    ascii('ô'), -- 244
    ascii('û'), -- 251
    ascii('à'), -- 224
    ascii('è'), -- 232
    ascii('ù'), -- 249
    ascii('ë'), -- 235
    ascii('ï'), -- 239
    ascii('ü')  -- 252

select 
    char(199),
    char(233),
    char(226),
    char(234),
    char(238),
    char(244),
    char(251),
    char(224),
    char(232),
    char(249),
    char(235),
    char(239),
    char(252)

So, for your example:

DECLARE @SomeString VARCHAR(20) = 'caf' + CHAR(233)
INSERT INTO #Table (value1) VALUES (@SomeString) -- café
4

in the most portable way possible, using only ASCII in my query

Requiring ASCII-only will make it harder to achieve this goal in a truly reliable manner. If you only have one database, or if you have multiple databases that all have the same default Collation, then using CHAR(asciiValue) as mentioned in @Nelson's answer would work. Just keep in mind that the character you get from using CHAR(someValue) depends on the default Collation of the "current" or "active" database that the query is running in. The Collation determines the code page, and that can be different per each DB. The following simulates what would happen if the same ASCII value were used in different DBs that had different default Collations that used different code pages:

DECLARE @Test TABLE
(
  [Latin1] VARCHAR(10) COLLATE Latin1_General_100_CI_AS_SC,
  [Hebrew] VARCHAR(10) COLLATE Hebrew_100_CI_AS_SC,
  [Greek] VARCHAR(10) COLLATE Greek_100_CI_AS_SC
);

SELECT CONVERT(BINARY(1), 233);

INSERT INTO @Test ([Latin1], [Hebrew], [Greek]) VALUES (0xE9, 0xE9, 0xE9);

SELECT * FROM @Test;
-- é      י       ι

when I send queries to a counterpart to perform database changes, the text file is opened and edited on different machines, and the special characters are damaged.

ASCII, is inherently difficult to make portable. ASCII text files have no ability to indicate what encoding was used to create them. The problem you are seeing is that the file is being written with one encoding / code page and then being read with another. If you open the file on your computer, does it look exactly as it should? Or has it already been altered? Because é exists in many code pages, I suspect that it still looks fine on your computer, which means it is being read with the wrong encoding.

One way to avoid this is to ensure that the person opening the file opens it with the correct encoding. You can use the File Open dialog in SSMS to select a file, but don't click the Open button. Instead, click the down arrow on the right side of the Open button and select Open With.... Next, select SQL Query Editor with Encoding and click OK. A new dialog will pop up and will be a drop-down of encodings. The drop-down will default to (Auto-Detect) but that won't work here. Just select the encoding that you are using when saving the file and then click OK.

An easier and more reliable way is to use a Unicode encoding (UTF-8 or UTF-16) with a Byte Order Mark. The Byte Order Mark (BOM) is 2 to 4 bytes at the very beginning of a file that indicates the encoding used to create the file. The BOM is used to auto-detect the encoding, which means that you don't need to tell the person you are sending the file to what encoding to use. And, it doesn't require that you use Unicode data in the script; you current INSERT statement with the VARCHAR data should be unaffected.

You can do this by going to File | Save As... Then, just like with the Open dialog, click on the down arrow on the Save button and select Save with Encoding. Then select either Unicode (UTF-8 with signature) - Codepage 65001 or Unicode - Codepage 1200 and then click OK.

1
  1. Use a function to convert from some ascii compatible representation to the respective encoding of your database, which hopefully is Unicode. There are several possibilities, e.g. HTML entities or URL encoding schemes.
  2. Check, how your database supports Unicode code-points natively, e.g. as references of the form &#999; or &#xhhhh; or U+9999.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.