Problem with Charindex function

Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
Bill
You will have to use TEXTPTR

>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
"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/

Problem with Charindex function

Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
"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/

Problem with Charindex function

Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR

>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
--
"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/

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