I am new to SQL Server and I am trying to write a trigger where I am doing a
delete on another table. I need to know whether this delete fails or not.
How can I achieve this?
TIA
Altmancheck the rowcount in the trigger
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||What do you mean by fail? If the delete fails with an error message, like a
foreign key constraint
violation, the trigger will not be fired. If you mean modify zero rows, chec
k @.@.ROWCOUNT the very
first thing you do in the triggers, or check the number of rows in the delet
ed table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Altman" <NotGiven@.SickOfSpam.com> wrote in message news:ODx2Uhl2FHA.3136@.TK2MSFTNGP09.phx.
gbl...
>I am new to SQL Server and I am trying to write a trigger where I am doing
a delete on another
>table. I need to know whether this delete fails or not. How can I achieve
this?
> --
> TIA
> Altman
>
>|||I mean that I have a trigger and inside of that trigger I am calling a
Delete from table ..... I need to know if the delete that I am calling
inside the trigger goes through.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eJ008Jm2FHA.3272@.TK2MSFTNGP09.phx.gbl...
> What do you mean by fail? If the delete fails with an error message, like
> a foreign key constraint violation, the trigger will not be fired. If you
> mean modify zero rows, check @.@.ROWCOUNT the very first thing you do in the
> triggers, or check the number of rows in the deleted table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Altman" <NotGiven@.SickOfSpam.com> wrote in message
> news:ODx2Uhl2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>|||It depends on what you mean by "goes though". If it generates and error, you
can catch that error in
your trigger code (@.@.error), just like you do in any TSQL code. And you can
get the number of rows
modified using @.@.ROWCOUNT. I suggest you check out the error handling articles at
www.sommarskog.se.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Altman" <NotGiven@.SickOfSpam.com> wrote in message news:O4jWsSm2FHA.2624@.TK2MSFTNGP09.phx.
gbl...
>I mean that I have a trigger and inside of that trigger I am calling a Dele
te from table ..... I
>need to know if the delete that I am calling inside the trigger goes throug
h.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eJ008Jm2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>
Showing posts with label ran. Show all posts
Showing posts with label ran. Show all posts
Thursday, March 22, 2012
Thursday, February 16, 2012
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
> |
>
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
> |
>
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
> |
>
charindex faster than like operator?
Hi ppl,
While I was working on the post "compare 2 strings" in my test db,
I came across this.
I ran these queries, the ones using the charindex and patindex were faster
than the like operator. I ran it in different tables with different inputs.
But same result.
I know server configs can affect the processing. But can some one run it in
their table and let me know. Plz. I am wordering whats the difference. Any
light on this?
Eager to know. Thanks a lot.
select top 100 * from tbl1 where namecol like '%food%'
select top 100 * from tbl1 where charindex('food',namecol) > 0
select top 100 * from tbl1 where patindex('%food%',namecol) > 0
select top 100 * from tbl1 where 'ab,cd' like '%' + namecol + '%'
select top 100 * from tbl1 where charindex(namecol,'ab,cd') > 0
select top 100 * from tbl1 where patindex('%' + namecol + '%','ab,cd') > 0use this.. and let me know if it works
select a.CoName from oldTbl a
where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName +
'%')
While I was working on the post "compare 2 strings" in my test db,
I came across this.
I ran these queries, the ones using the charindex and patindex were faster
than the like operator. I ran it in different tables with different inputs.
But same result.
I know server configs can affect the processing. But can some one run it in
their table and let me know. Plz. I am wordering whats the difference. Any
light on this?
Eager to know. Thanks a lot.
select top 100 * from tbl1 where namecol like '%food%'
select top 100 * from tbl1 where charindex('food',namecol) > 0
select top 100 * from tbl1 where patindex('%food%',namecol) > 0
select top 100 * from tbl1 where 'ab,cd' like '%' + namecol + '%'
select top 100 * from tbl1 where charindex(namecol,'ab,cd') > 0
select top 100 * from tbl1 where patindex('%' + namecol + '%','ab,cd') > 0use this.. and let me know if it works
select a.CoName from oldTbl a
where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName +
'%')
Subscribe to:
Posts (Atom)