Problem with CHARINDEX

Hi,
CHARINDEX does not seem to work on nvarchar datatype.
Code:
declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
select @.pCode = 'ABZC001'
select @.pInfo = 'ABZC0011,CBQA03,FG0023'
select @.index = charindex(@.pCode,@.pInfo)
if @.index = 0
begin
...
...
end
charindex returns 1, though a perfect match is not found!
Any suggestions, would sure be, of help.
Thanks.
kdkd
I'm not sure what are you trying to do?
What does 'perfect match' mean?
"kd" <kd@.discussions.microsoft.com> wrote in message
news:36373C11-8C5D-447D-9BC3-B02AA6943BD3@.microsoft.com...
> Hi,
> CHARINDEX does not seem to work on nvarchar datatype.
> Code:
> declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
> select @.pCode = 'ABZC001'
> select @.pInfo = 'ABZC0011,CBQA03,FG0023'
> select @.index = charindex(@.pCode,@.pInfo)
> if @.index = 0
> begin
> ....
> ....
> end
> charindex returns 1, though a perfect match is not found!
> Any suggestions, would sure be, of help.
> Thanks.
> kd|||Hi
There is a perfect match, but you may want add a comma to the end of @.pcode
to make sure that is a match for the whole string in the comma separated
list!!!
declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
select @.pCode = 'ABZC001,'
select @.pInfo = 'ABZC0011,CBQA03,FG0023'
select @.index = charindex(@.pCode,@.pInfo)
select @.INDEX
/*
0
(1 row(s) affected)
*/
John
"kd" <kd@.discussions.microsoft.com> wrote in message
news:36373C11-8C5D-447D-9BC3-B02AA6943BD3@.microsoft.com...
> Hi,
> CHARINDEX does not seem to work on nvarchar datatype.
> Code:
> declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
> select @.pCode = 'ABZC001'
> select @.pInfo = 'ABZC0011,CBQA03,FG0023'
> select @.index = charindex(@.pCode,@.pInfo)
> if @.index = 0
> begin
> ....
> ....
> end
> charindex returns 1, though a perfect match is not found!
> Any suggestions, would sure be, of help.
> Thanks.
> kd|||Hi John,
Adding a comma at the end of @.pCode seems to be a good idea. It would also
be required to add a comma at the end of the list @.pInfo, to make the last
string to be available for matching.
No comma at the end of the list would indicate the end of the list. If the
above solution is adopted, then, how to designate the end of the list?
kd.
"John Bell" wrote:

> Hi
> There is a perfect match, but you may want add a comma to the end of @.pcod
e
> to make sure that is a match for the whole string in the comma separated
> list!!!
> declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
> select @.pCode = 'ABZC001,'
> select @.pInfo = 'ABZC0011,CBQA03,FG0023'
> select @.index = charindex(@.pCode,@.pInfo)
> select @.INDEX
> /*
> --
> 0
> (1 row(s) affected)
> */
> John
>
> "kd" <kd@.discussions.microsoft.com> wrote in message
> news:36373C11-8C5D-447D-9BC3-B02AA6943BD3@.microsoft.com...
>
>|||Hi
You don't have to add a comma permanently!!
declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
select @.pCode = 'FG0023,'
select @.pInfo = 'ABZC0011,CBQA03,FG0023'
select @.index = charindex(@.pCode,@.pInfo+',')
select @.INDEX
You may also want to look at http://www.sommarskog.se/arrays-in-sql.html
John
"kd" <kd@.discussions.microsoft.com> wrote in message
news:E0354B88-6FA2-4504-A57F-F001686E28E0@.microsoft.com...
> Hi John,
> Adding a comma at the end of @.pCode seems to be a good idea. It would also
> be required to add a comma at the end of the list @.pInfo, to make the last
> string to be available for matching.
> No comma at the end of the list would indicate the end of the list. If the
> above solution is adopted, then, how to designate the end of the list?
> kd.
> "John Bell" wrote:
>|||I think you may be operating on a fundememental misconception about what
CharIndex () is for, and what it does...
It is simply to find the index, (or position) of one string, within another
SINGLE string. The function does not consider the target string as a set o
f
individual strings, but only as one single long string... To illustrate:
if you Look for' 11,,CBQ' within your string. it will return the value of 7
.
declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
select @.pCode = '11,CBQ'
select @.pInfo = 'ABZC0011,CBQA03,FG0023'
select @.index = charindex(@.pCode,@.pInfo)
select @.INDEX
-- *************************
What you want I thing, is to use the SQL word "IN"
As:
declare @.pCode as nvarchar(50)
select @.pCode = 'ABZC0011'
If @.pCode IN ('ABZC0011', 'CBQA03', 'FG0023')
Print 'Found it'
Else
Print 'Not there'
"kd" wrote:
> Hi John,
> Adding a comma at the end of @.pCode seems to be a good idea. It would also
> be required to add a comma at the end of the list @.pInfo, to make the last
> string to be available for matching.
> No comma at the end of the list would indicate the end of the list. If the
> above solution is adopted, then, how to designate the end of the list?
> kd.
> "John Bell" wrote:
>|||What do you mean designate the end of the list? Wouldn't the lack of
follow-on data tend to indicate the end of the list :) As John also
replied, the comma doesn't have to be permanent.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"kd" <kd@.discussions.microsoft.com> wrote in message
news:E0354B88-6FA2-4504-A57F-F001686E28E0@.microsoft.com...
> Hi John,
> Adding a comma at the end of @.pCode seems to be a good idea. It would also
> be required to add a comma at the end of the list @.pInfo, to make the last
> string to be available for matching.
> No comma at the end of the list would indicate the end of the list. If the
> above solution is adopted, then, how to designate the end of the list?
> kd.
> "John Bell" wrote:
>