You need to take the data from UTF-8 and convert it into UCS-2LE using something like iconv. For example, using the character in your example:
echo "010000: dcb3" | xxd -r -s -0x10000 | iconv -f "UTF-8" -t "UCS-2LE" | xxd
0000000: 3307
Now I'm not sure what character UTF-8 \xdcb3 is, but apparently it's correct translation to UCS-2LE is \U0733
. If you have \0xDCB3
in the SQL Server it means it was not translated into UCS-2LE before import. You should not have surrogates in the NVARCHAR fields, UCS-2 is "surrogate agnostic". See UCS-2 vs. UTF-16 (not quite Kramer vs. Kramer).
I'm not an expert in the MySQL tool set so I can't say what step is missing that was supposed to do the iconv.
Update
to locate the records with surrogates you must turn to the binary representation, since any character function will threat the surrogates as 'special':. Luckily the string manipulation functions work on binary too with the expected semantics. Eg. CHARINDEX:
insert into test(a) values (N'a');
insert into test(a) values (NCHAR(0xdc83));
insert into test(a) values (N'b');
go
select * from test where charindex(0x83dc, cast(a as varbinary(8000))) > 0;
Add a persistent calculated field that contains a CHECKSUM
on the 5 fields, and use that to perform the comparisons.
The CHECKSUM
field will be unique for that specific combination of fields, and is stored as an INT
that results in a much easier target for comparisons in a WHERE
clause.
USE tempdb; /* create this in tempdb since it is just a demo */
CREATE TABLE dbo.t1
(
Id bigint constraint PK_t1 primary key clustered identity(1,1)
, Sequence int
, Parent int not null constraint df_T1_Parent DEFAULT ((0))
, Data1 varchar(20)
, Data2 varchar(20)
, Data3 varchar(20)
, Data4 varchar(20)
, Data5 varchar(20)
, CK AS CHECKSUM(Data1, Data2, Data3, Data4, Data5) PERSISTED
);
GO
INSERT INTO dbo.t1 (Sequence, Parent, Data1, Data2, Data3, Data4, Data5)
VALUES (1,1,'test','test2','test3','test4','test5');
SELECT *
FROM dbo.t1;
GO
/* this row will NOT get inserted since it already exists in dbo.t1 */
INSERT INTO dbo.t1 (Sequence, Parent, Data1, Data2, Data3, Data4, Data5)
SELECT 2, 3, 'test', 'test2', 'test3', 'test4', 'test5'
WHERE Checksum('test','test2','test3','test4','test5') NOT IN (SELECT CK FROM t1);
/* still only shows the original row, since the checksum for the row already
exists in dbo.t1 */
SELECT *
FROM dbo.t1;
In order to support a large number of rows, you'd want to create an NON-UNIQUE index on the CK
field.
By the way, you neglected to mention the number of rows you are expecting in this table; that information would be instrumental in making great recommendations.
In-row data is limited to a maximum of 8060 bytes, which is the size of a single page of data, less the required overhead for each page. Any single row larger than that will result in some off-page storage of row data. I'm certain other contributors to http://dba.stackexchange.com can give you a much more concise definition of the engine internals regarding storage of large rows. How big is your largest row, presently?
If items in Data1, Data2, Data3...
have the same values occurring in a different order, the checksum will be different, so you may want to take that into consideration.
Following a brief discussion with the fantastic Mark Storey-Smith on The Heap, I'd like to offer a similar, although potentially better choice for calculating a hash on the fields in question. You could alternately use the HASHBYTES()
function in the calculated column. HASHBYTES()
has some gotchas, such as the necessity to concatenate your fields together, including some type of delimiter between the field values, in order to pass HASHBYTES()
a single value. For more information about HASHBYTES()
, Mark recommended this site. Clearly, MSDN also has some great info at http://msdn.microsoft.com/en-us/library/ms174415.aspx
Best Answer
Since there are a fair number of solutions I'm going to go with the "critique" part of your question. A couple of notes: I've fixed some typos and noted where I did. If I'm wrong about them being a typo mention it in the comments and I'll explain what's going on. I'm going to point out several things that you may already know, so please don't take offense if I did. Some comments may seem picky but I don't know where you are in your journey so have to assume you are just starting out.
ALWAYS include the length with a
char
orvarchar
definition. Aaron Bertrand talks about it in depth here. He is talking aboutvarchar
but the same goes forchar
. I'd use avarchar(255)
for this if you only want relatively short strings or maybe avarchar(8000)
for larger ones or evenvarchar(max)
.Varchar
is for variable length stringschar
is only for fixed ones. Since you aren't sure of the length of string being passed in usevarchar
. Also it'sbinary
notbin
.Next you don't need to put all of those variables as parameters. Declare them within your code. Only put something in the parameter list if you plan on passing it in or out. (You'll see how this looks at the end.) Also you have
@StringLeftLength
but never use it. So I'm not going to declare it.The next thing I'm going to do is re-format a bit to make a few things obvious.
If you look at the way I did the indenting you'll notice that I have this:
That's because commands like
WHILE
andIF
only affect the first line of code after them. You have to use aBEGIN .. END
block if you want multiple commands. So fixing that we get:You'll notice that I only added a
BEGIN .. END
block in theIF
. That's because even though theIF
statement is multiple lines long (and even contains multiple commands) it is still a single statement (covering everything performed in theIF
and theELSE
portions of the statement).Next you'll get an error after both of your
RETURNs
. You can return a variable OR a literal. You can't set the variable and return it at the same time.Now we are into logic. First let me point out that the
LEFT
andRIGHT
functions you are using are great, but they are going to give you the number of characters you pass in from the requested direction. So let's say you passed in the word "test". On the first pass you are going to get this (removing variables):Obviously that isn't what you expected. You would really want to use
substring
instead. Substring lets you pass in not only the starting point but the length. So you would get:Next you are incrementing the variables you use in your loop only in one condition of the IF statement. Pull the variable incrementing out of that structure entirely. That is going to require an additional
BEGIN .. END
block, but I do get to remove the other one.You need to change your
WHILE
condition to allow for the last test.And last but not least, the way it stands now we don't test the last character if there are an odd number of characters. For example with 'ana' the
n
isn't tested. That's fine but it does me we need to account for a single letter word (if you want it to count as a positive that is). So we can do that by setting the value up front.And now we finally have:
One last comment. I'm a big fan of formatting in general. It can really help you to see how your code works and help to point out possible mistakes.
Edit
As Sphinxxx mentioned we still have a flaw in our logic. Once we hit the
ELSE
and set@Palindrome
to 0 there is no point in continuing. In fact at that point we could justRETURN
.Given that we are now only using
@Palindrome
for "it's still possible this is a palindrome" there is really no point in having it. We can get rid of the variable and switch our logic to short circuit on failure (theRETURN 0
) andRETURN 1
(a positive response) only if it makes it all the way through the loop. You'll notice this actually simplifies our logic somewhat.