question

redder avatar image
redder asked

Search a string the symbol that looks like a small square

Data in a string looks like a small square. I am not sure if it is a spacebar or enter key or what. But is there a way to search for it in SQL? #2 and #3 also has some special characters that is causing my XML data type to get an illegal character error message. Not sure if there is a way to find symbols that would fail using XML data type. Thx Ex: 1. ( J 3 C ' * ' H ' J F J H , J 1 3 J , NJ 08854, United States 2. PO Box 416, Ho olehua, HI 96729-0416, United States 3. # H C 3 ( 1 J / , , E ' 3 ' * 4 H 3 J * 3 01569, United States
sql-server-2005xml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
The square you see is represented on my screen as a diamond with a question mark in it... This is a placeholder character that generally indicates a non-printable character. It looks in this case as though something further up the line is failing to properly handle Unicode.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
+1 I'd agree - it's a non-printable, but by the time it hits SQL it's been 'translated' into something that SQL can handle, hence the square. As @ThomasRushton says it's further up the line that the issue occurs
2 Likes 2 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
often see this if MSOffice products get involved in data handling. Paste into Notepad and then copy out often fixes it for small data sets
2 Likes 2 ·
Fatherjack avatar image
Fatherjack answered
Its almost certainly a non-printing character. To see what is meant by this take a look at the results of this script CREATE TABLE #CharAscii ( ColA VARCHAR(20) ) INSERT INTO #CharAscii ( [ColA] ) VALUES ( 'Bill' -- ColA - varchar(20) ), ( 'Bob' -- ColA - varchar(20) ), ( 'Larry' -- ColA - varchar(20) ), ( 'George' -- ColA - varchar(20) ), ( 'William' -- ColA - varchar(20) ), ( ' William' -- ColA - varchar(20) ), ( CHAR(0) + 'William' -- ColA - varchar(20) ), ( CHAR(1) + 'William' -- ColA - varchar(20) ), ( CHAR(2) + 'William' -- ColA - varchar(20) ), ( CHAR(3) + 'William' -- ColA - varchar(20) ), ( CHAR(4) + 'William' -- ColA - varchar(20) ), ( CHAR(5) + 'William' -- ColA - varchar(20) ), ( CHAR(6) + 'William' -- ColA - varchar(20) ), ( CHAR(7) + 'William' -- ColA - varchar(20) ), ( CHAR(8) + 'William' -- ColA - varchar(20) ) SELECT [ColA] , LEFT([ColA], 1) AS [First Character] , ASCII(LEFT([ColA], 1)) AS ASCII_Number , LEN([ColA]) FROM #CharAscii SELECT * FROM [#CharAscii] AS ca WHERE [ca].[ColA] LIKE '%' + CHAR(3) + '%' DROP TABLE #CharAscii You will probably need to identify what the character is and remove it in order for the XML to parse. Use the ASCII function to identify what the character is and then search for it using the CHAR function
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.