Issues reading a string with weird characters in SQL Server 2008 and previous versions -


here issue today: load info text files sql server table. thing field realized string value little bit weird.

when query table sql server client 2008 or previous, got result set:

enter image description here

even if try run query 1 below, resultset empty:

select replace(ltrim(rtrim(cust_po)), '  ', ' ')  dbo.test_char cust_po '%076929%'

the weird thing is: sql server 2016, opening table looks normal, although query doesn't return results either:

enter image description here

now, following couple of instruction @solomonrutzky have performed query below result in varbinary

enter image description here

the new result : 0x300037003600390032003900bc05bc05bc05bc05bc05bc05bc05bc05bc05

how varchar without weird chars or space or wherever is?

what going on?

now varbinary representation of data has been posted, issue clearer.

as can see, 2 lines "odd" characters are:

0x300037003600390032003900bc05bc05bc05bc05bc05bc05bc05bc05bc05

this column nvarchar means encoding utf-16 little endian. being utf-16 means @ each block of 2 bytes (characters either 1 or 2 sets of 2-byte blocks), , being little endian means bytes in each 2-byte block in reverse order. meaning, first character 3000 (which 2 bytes), , equates code point u+0030, number 0. next character 3700 code point u+0037 number 7. , on down through 3900 final 9 in value.

but 9 sets of bc05 code point u+05bc, hebrew point dagesh or mapiq. , gets interesting there 3 separate things going on here:

  1. what displayed function of font being used. , not fonts handle characters correctly, or have mappings characters. why see series of circles left in results grid, , single dot on far left in row editor (hard see, if don't know it, closely in row editor image , top 2 lines both have tiny dot left of 0).
  2. hebrew right-to-left language, explains why circles in results grid on left, though characters in data come after numbers.
  3. this particular character combining character, means supposed attach character precedes it. since different fonts display characters differently variation seeing between results grid , row editor. results grid font not seem handling character correctly, while row editor does. but, in handling correctly, see combining character not allow showing multiples, see single dot on far left instead of 9 dots (but 9 there, 1 on top of other.

to see in action, following query:

select n'4   11' + replicate(nchar(0x05bc), 10) + n'  88  f ';

returns:

4 11ּּּּּּּּּּ 88 f

notice single dot on left side of second 1. there single dot though string has 10 of them (due replicate). well, browser showing, captured following image:

enter image description here

yet see following in results grid:

enter image description here

notice how dot left of first, not second, 1, , placement of 11 between 88 , f,

and, if copy , paste results grid query editor, see:

enter image description here

notice there several red dots.

why doesn't where cust_po '%076929%' return rows?

this due string comparison doing should , applying linguistic rules. doesn't matter order bytes in, matters how rendered string viewed human reading perspective. , since particular character combining character, not come after preceding character, part of it. meaning, second 9 in 076929 value isn't 9 anymore, 9 + dagesh. 

select 1 n'123' + replicate(nchar(0x05bc), 5) n'%123' + replicate(nchar(0x05bc), 4) + n'%'; -- no rows returned  select 2 n'123' + replicate(nchar(0x05bc), 5) n'%123' + replicate(nchar(0x05bc), 5) + n'%'; -- 2  select 3 n'9' + nchar(0x05bc) = n'9' -- no rows returned

how rid of characters?

assuming u+05bc character issue in data, can simple replace. have tried this, replace isn't finding match. when happens, need use binary collation shown here:

select replace(n'4   11' + replicate(nchar(0x05bc), 5) + n'  88  f ',                nchar(0x05bc) collate latin1_general_100_bin2,                n'~'); -- 4   11~~~~~  88  f

Comments

Post a Comment

Popular posts from this blog

PHP and MySQL WP -

i trying accomplish 2 things here code. writing php directly wp page using insert php plugin. 1) query mysql database , return results 1 column 4 columns. 2) make each result own hyperlink directs user new wp page runs new query on page. i able query show results , turn results dynamic hyperlink, cannot formatting down. right returns result 1 column. here have: [insert_php] $servername = "localhost"; $username = "login"; //edited privacy $password = "password"; //edited privacy $database = "database"; //edited privacy // create connection $conn = new mysqli($servername, $username, $password, $database); // check connection if ($conn->connect_error) { die("connection failed: " . $conn->connect_error); } echo "connected successfully"; //get results database $result = mysqli_query($conn,"select mine mines order mine"); $all_property = array(); //declare array saving property //showing proper...
Read more

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

go - golang pprof for c library code -

i have been trying profile golang application pprof, seems details of c code(linked static library) missed in output result. flame graph from frame graph can see c code time consuming reported within "runtime._externalcode", no details @ all. , sure static library compiled debug enable. so there way make pprof details(like stack trace) of 3rd party c code? or there other golang profiling tool this?
Read more