CHARINDEX with Unicode

To test an idea, I ran the following script in Query Analyser:
DECLARE @.w nvarchar(100)
SET @.w = 'Peter' + NCHAR(65535) + 'Hyssett'
SELECT CHARINDEX(NCHAR(65535), @.w, 1)
This produced a result of 1. Replacing NCHAR(65535) with NCHAR(696) produced
the expected result of 6, while NCHAR(697) produced a result of 1.
Can someone please explain why I did not get a result of 6 for any value of
NCHAR(n) above 255?
Thanks.
--
Peter Hyssett65535 (0xFFFF) is an undefined Unicode code point. SQL essentially ignores
undefined characters in string searches.
For example:
DECLARE @.w nvarchar(100)
SET @.w = 'abc' + NCHAR(65535) + 'def'
SELECT @.w
SELECT CHARINDEX ('cd', @.w, 1)
This returns 3. The undefined character that falls in between "c" and "d"
is ignored, allowing the search for the string "cd" to succeed. Are you
really searching for 0xFFFF, or is there some other character code point
that you are having problems with?
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: CHARINDEX with Unicode
| thread-index: AcTbDqgFDySUytOFRH+uI0ESlvAqnA==| X-WBNR-Posting-Host: 195.92.194.12
| From: "=?Utf-8?B?UGV0ZXIgSHlzc2V0dA==?="
<PeterHyssett@.discussions.microsoft.com>
| Subject: CHARINDEX with Unicode
| Date: Sun, 5 Dec 2004 13:09:06 -0800
| Lines: 14
| Message-ID: <ABA6950A-BD97-46B6-9421-418AED2999FA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA0
3.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:369843
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| To test an idea, I ran the following script in Query Analyser:
| DECLARE @.w nvarchar(100)
| SET @.w = 'Peter' + NCHAR(65535) + 'Hyssett'
| SELECT CHARINDEX(NCHAR(65535), @.w, 1)
|
| This produced a result of 1. Replacing NCHAR(65535) with NCHAR(696)
produced
| the expected result of 6, while NCHAR(697) produced a result of 1.
|
| Can someone please explain why I did not get a result of 6 for any value
of
| NCHAR(n) above 255?
|
| Thanks.
| --
| Peter Hyssett
||||Thanks, Bart.
No, I just wanted to use a Unicode character as a delimiter to avoid the
hassle of finding delimiter characters in ASCII data. I now know to choose a
Unicode character that actually exists as the delimiter.
"Bart Duncan [MSFT]" wrote:
> 65535 (0xFFFF) is an undefined Unicode code point. SQL essentially ignores
> undefined characters in string searches.
> For example:
> DECLARE @.w nvarchar(100)
> SET @.w = 'abc' + NCHAR(65535) + 'def'
> SELECT @.w
> SELECT CHARINDEX ('cd', @.w, 1)
> This returns 3. The undefined character that falls in between "c" and "d"
> is ignored, allowing the search for the string "cd" to succeed. Are you
> really searching for 0xFFFF, or is there some other character code point
> that you are having problems with?
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: CHARINDEX with Unicode
> | thread-index: AcTbDqgFDySUytOFRH+uI0ESlvAqnA==> | X-WBNR-Posting-Host: 195.92.194.12
> | From: "=?Utf-8?B?UGV0ZXIgSHlzc2V0dA==?="
> <PeterHyssett@.discussions.microsoft.com>
> | Subject: CHARINDEX with Unicode
> | Date: Sun, 5 Dec 2004 13:09:06 -0800
> | Lines: 14
> | Message-ID: <ABA6950A-BD97-46B6-9421-418AED2999FA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA0
> 3.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:369843
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | To test an idea, I ran the following script in Query Analyser:
> | DECLARE @.w nvarchar(100)
> | SET @.w = 'Peter' + NCHAR(65535) + 'Hyssett'
> | SELECT CHARINDEX(NCHAR(65535), @.w, 1)
> |
> | This produced a result of 1. Replacing NCHAR(65535) with NCHAR(696)
> produced
> | the expected result of 6, while NCHAR(697) produced a result of 1.
> |
> | Can someone please explain why I did not get a result of 6 for any value
> of
> | NCHAR(n) above 255?
> |
> | Thanks.
> | --
> | Peter Hyssett
> |
>