sql varchar code example

Example 1: var in sql

One way of doing this is to use database variables

create table testcalc (
   id int(11) not null auto_increment,
   num1 int(11) default null,
   num2 int(11) default null,
   num3 int(11) default null,
   num4 int(11) default null,
   primary key(id)
);

insert into testcalc values
(default, 1, 2, 3, 4),
(default, 5, 10, 15, 20);
Then you can get the same results as in your example by storing the calculation results in variable syntax like this

@youVar := (calc) as resultName01
Then it will be available to following calculations to use like this

(@youVar + newCalc) as resultName02
We can apply it to your example like this

select
   id,
   num1,
   num2,
   num3,
   num4,
   @1plus2 := (num1 + num2) as 1plus2,                   # create var01
   @1plus2mult3 := (@1plus2 * num3) as 1plus2mult3,      # create var02 using var01
   @sumOfCalc := (@1plus2 + @1plus2mult3) as sumOfCalc   # create var03 using var01 and var02
from testcalc;

Example 2: store unicode characters in sql varchar() fields

The '?' character replaced the original value because there was no equivalent character in the collation code page and is physically stored in the column instead of the original value.
Sorry to say but the original value is lost.
share  improve this answer  follow 
answered Apr 27 '19 at 12:34

SHORT ANSWER: The correct answer is do you have backups before this change?

UNICODE IS A SPECIAL DATATYPE IN SQL SERVER

As stated in one answer, UNICODE is a universal coding scheme designed to work with any other “coding page” Microsoft DOCS - UNICODE SUPPORT. Think of Unicode as a large character mapping scheme that contains many non-standard characters of foreign languages.

SQL Server treats Unicode specially, with datatypes like NCHAR (fixed length), NVARCHAR (variable Unicode length) that will translate anywhere.

Additionally, and very importantly, UNICODE uses two character lengths compared to regular non-Unicode Characters. This is because that “map” has to be big enough to work with the special sizes of Unicode characters.

The storage size is two times n bytes + 2 bytes. nchar and NVARCHAR - Microsoft Docs This is twice the size of regular char/varchar and covers surrogate-pair key characters (not necessarily unique to Uni-code).

PROBLEM: VARCHAR HAS NO MATCH TO UNICODE BUT INSERTED ANYWAYS

Basically, the reason for “?” is because there is no matching equivalent for varchar was found. Unfortunately, SQL Server does not have a native way of preventing or warning you about these implicit conversions so the data is lost during insertion or modification.

SOLUTION: RESTORE OR RECREATE

Thus, you must restore or recreate the entries.

Was this a recent change? Restore to the latest backup before the change. Was this a design that has been around for a while? Then, see if the rows can be recreated. Otherwise, there is no hope of recovering data that was lost.