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
> |
>

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 Hyssett
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!TK2MSFT NGP08.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!TK2MSFT NGP08.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
> |
>

char nchar vchar nvchar !?

Hello
in MS SQL 2000 for text it is possible ti use
char
nchar
varchar
nvarchar
what must be used and for what ?
I want to store normal text with occidental char (accents)
thank youHello
in MS SQL 2000 for text it is possible ti use
char
nchar
varchar
nvarchar

what must be used and for what ?
I want to store normal text with occidental char (accents)

thank you
Varchar and nvarchar provide more efficient storage than char and nchar.
Text and ntext is used to store text that would be longer than 8000 characters.
You would use nchar,nvarchar and ntext only if you want to use special characters(Unicode character set).

See this-
Char - fixed no of non unicode characters -max size 8000 characters if you assign char(10) and store 7 characters in it, it will store 3 blank spaces in it.But if you use varchar(10) and do the same thing that will not happen,it will store only 7 characters.

And Unicode is a character set that is capable of displaying and storing 65,536 different characters,whereas a standard character set can store and display only 256 different characters.Unicode uses 2 bytes and is very useful for international companies.Hope this will help you.
Joydeep|||then nvarchar seems the best for my needs

thank you|||You would be well-served by reviewing descriptions of the different datatypes in Books Online.|||yes i found it

thank you