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

problem with bit datatype conversion

Hi All,

I have create following table and inserted few records

Code Snippet

USE

---

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_position](
[part] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[price] [money] NOT NULL,
[opt] [bit] NOT NULL
) ON [PRIMARY]

I am executing following procedure to update the table.

Code Snippet

USE

---

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )
AS
BEGIN
declare @.sSQL as nvarchar(max);
SET @.sSQL = 'update tbl_position set opt=@.OPT,price=@.PRICE WHERE part = '+ @.part;
exec sp_executesql @.sSQL
END

ex:

Code Snippet

exec PROC1 1,2.50,0

but I am getting following error

Code Snippet

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@.OPT".

I am struck up with this query .

Please Help me regarding this issue.

Regards
Gomaz

Code Snippet

CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )

AS

BEGIN

declare @.sSQL as nvarchar(max);

SET @.sSQL = 'update tbl_position set opt=' +cast(@.OPT as char(1) )+',price='+@.PRICE+' WHERE part = '+ @.part;

exec sp_executesql @.sSQL

END

sp_executesql execute query in different bacth. As you couldn't use opt=@.OPT

Problem with AddNew on SQL server in c++ 2003

Hello,
I created a very simple database with only one table (Records) and on
that table only one column (Category datatype nvarchar).
I am trying to use the AddNew ado example found on msnd but it always
inserts a null value instead of the value I am trying to insert.
All my HRESULTs say S_OK but the CategoryStatus is alway 3 (which is
null).
I am using UNICODE.
I can insert records just fine if I use the INSERT INTO command but I
am not having any luck with the AddNew API.
Can anyone help?
Here is my code:
class CJournalRecord :public CADORecordBinding
{
BEGIN_ADO_BINDING(CJournalRecord)
ADO_VARIABLE_LENGTH_ENTRY2(1, adVarChar, Category, sizeof(Category),
CategoryStatus, TRUE)
END_ADO_BINDING()
public:
CString Category;
ULONG CategoryStatus;
};
HRESULT hr = S_OK;
_RecordsetPtr pRstEvents = NULL;
IADORecordBinding *picRs = NULL;
hr = pRstEvents.CreateInstance(__uuidof(Recordset));
if (hr != S_OK)
{
}
else
{
//the connection is already open
CJournalRecord newEvent;
hr = pRstEvents->Open(_T("Records"),_variant_t((IDispatch *)
connection, true),adOpenKeyset,adLockOptimistic,adCm
dTable);
//Open an IADORecordBinding interface pointer which we'll use for
Binding Recordset to a class
hr =
pRstEvents-> QueryInterface(__uuidof(IADORecordBindin
g),(LPVOID*)&picRs);
hr = picRs->BindToRecordset(&newEvent);
newEvent.Category = _T("SeeYou");
newEvent.CategoryStatus = adFldNull;
if(hr!=S_OK)
{
}
else
{
hr = picRs->AddNew(&newEvent);
int status = (int)newEvent.CategoryStatus;
//hr = pRstEvents->Update();
}
}Try creating a stored procedure and executing that. Using AddNew from the
middle tier is kinda hokey.
<roberta.coffman@.emersonprocess.com> wrote in message
news:1134768159.533377.64050@.g43g2000cwa.googlegroups.com...
> Hello,
> I created a very simple database with only one table (Records) and on
> that table only one column (Category datatype nvarchar).
> I am trying to use the AddNew ado example found on msnd but it always
> inserts a null value instead of the value I am trying to insert.
> All my HRESULTs say S_OK but the CategoryStatus is alway 3 (which is
> null).
> I am using UNICODE.
> I can insert records just fine if I use the INSERT INTO command but I
> am not having any luck with the AddNew API.
> Can anyone help?
> Here is my code:
> class CJournalRecord :public CADORecordBinding
> {
> BEGIN_ADO_BINDING(CJournalRecord)
> ADO_VARIABLE_LENGTH_ENTRY2(1, adVarChar, Category, sizeof(Category),
> CategoryStatus, TRUE)
> END_ADO_BINDING()
> public:
> CString Category;
> ULONG CategoryStatus;
> };
> HRESULT hr = S_OK;
> _RecordsetPtr pRstEvents = NULL;
> IADORecordBinding *picRs = NULL;
> hr = pRstEvents.CreateInstance(__uuidof(Recordset));
> if (hr != S_OK)
> {
> }
> else
> {
> //the connection is already open
> CJournalRecord newEvent;
> hr = pRstEvents->Open(_T("Records"),_variant_t((IDispatch *)
> connection, true),adOpenKeyset,adLockOptimistic,adCm
dTable);
> //Open an IADORecordBinding interface pointer which we'll use for
> Binding Recordset to a class
> hr =
> pRstEvents-> QueryInterface(__uuidof(IADORecordBindin
g),(LPVOID*)&picRs);
> hr = picRs->BindToRecordset(&newEvent);
> newEvent.Category = _T("SeeYou");
> newEvent.CategoryStatus = adFldNull;
> if(hr!=S_OK)
> {
> }
> else
> {
> hr = picRs->AddNew(&newEvent);
> int status = (int)newEvent.CategoryStatus;
> //hr = pRstEvents->Update();
> }
> }
>|||I'm not sure binding to the CString Category works here, also
sizeof(Category) is pretty meaningless. Suggest you change
CString to a fixed sized array to mirror the size of the column
in your table, i.e. change
CString Category;
to
CHAR Category[80];

problem with convert 2000 server 2000

we want to insert date into table testDate in Datetime or another dataType in field testDate and extract it in hijry Date format And compare it with two Date.
acording to the articles we insert it in to the table in nchar dataType and then we want to extract it in datetime dataType using convert function in sql server :

(( SELECT CONVERT(datetime, ' testDate', 131)
FROM testDate ))
that gives this error:

(( Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string. ))

if we want to insert Date in datetime dataType it enter in gregorian Date format.
while we want Date in hijry format.try this, it may work? You will have to sort the hours and minutes etc...

convert( smalldatetime, ( datename( yy, testdate) + ' '
+ datename( mm, testdate) + ' '
+ datename( dd, testdate)
))

problem updating text datatype

Hi All:
I am having trouble updating a TEXT column (I know that TEXT is a blob but I
am dealing with very large amounts of data that VARCHAR can not hold!) What
I want to happen is all rows from TableA that meet a certain criteria to be
inserted into TableB but if it already exists in TableB then update it
instead - this sounds simple enough and is working fine except that one of
the fields is TEXT datatype. So when I try to update I get an error "The
text, ntext, and image datatypes are invalid in this subquery or aggregate
expression." (Apparently you can not do an update select with a text
datatype) I did a search and found that I need to be using the UPDATETEXT
function - however I am not quite sure how this works. Any suggestions
would be greatly appreciated!
TableA
ID INT
MenuID INT
Content TEXT
TableB
ID INT
MenuID INT
Content TEXT
Thanks!Nancy,
Without seeing the query, it's impossible to know what the
problem is. Post the query that is causing the error, at
least, if you want a more specific answer.
In any case, it is certainly possible to update [text]
with an UPDATE statement. In general, you may want
something like this:
update TableB set
Content = TableA.Content
from TableA join TableB
on TableA.ID = TableB.ID
and TableA.MenuID = TableB.MenuID
insert into TableB(ID, MenuID, Content)
select ID, MenuID, Content
from TableA
where not exists (
select * from TableB as B2
where B2.ID = TableA.ID
and B2.MenuID = TableA.MenuID
)
Steve Kass
Drew University
Nancy Shelley wrote:

> Hi All:
> I am having trouble updating a TEXT column (I know that TEXT is a blob but
I
> am dealing with very large amounts of data that VARCHAR can not hold!) Wha
t
> I want to happen is all rows from TableA that meet a certain criteria to
be
> inserted into TableB but if it already exists in TableB then update it
> instead - this sounds simple enough and is working fine except that one of
> the fields is TEXT datatype. So when I try to update I get an error "The
> text, ntext, and image datatypes are invalid in this subquery or aggregate
> expression." (Apparently you can not do an update select with a text
> datatype) I did a search and found that I need to be using the UPDATETEXT
> function - however I am not quite sure how this works. Any suggestions
> would be greatly appreciated!
>
> TableA
> ID INT
> MenuID INT
> Content TEXT
> TableB
> ID INT
> MenuID INT
> Content TEXT
> Thanks!
>|||Hi Steve:
Thanks for the quick response. Supplying BLOB columns with text or image
data that's less than or equal to 8000 bytes in size is as straightforward
as updating any other type of column (you can use insert/update) but when
values are larger than 8000 you need to use updatetext or writetext. This
is what I was having trouble with - I know the syntax for updating my table
the regular way I just wasn't sure about using updatetext or writetext.
I ended up solving my own problem - my pointer was invalid. I was getting
an invalid pointer reference to the text column.
Thanks for your help!
Nancy
"Steve Kass" <skass@.drew.edu> wrote in message
news:OqsmT2jWFHA.3488@.tk2msftngp13.phx.gbl...
> Nancy,
> Without seeing the query, it's impossible to know what the
> problem is. Post the query that is causing the error, at
> least, if you want a more specific answer.
> In any case, it is certainly possible to update [text]
> with an UPDATE statement. In general, you may want
> something like this:
>
> update TableB set
> Content = TableA.Content
> from TableA join TableB
> on TableA.ID = TableB.ID
> and TableA.MenuID = TableB.MenuID
> insert into TableB(ID, MenuID, Content)
> select ID, MenuID, Content
> from TableA
> where not exists (
> select * from TableB as B2
> where B2.ID = TableA.ID
> and B2.MenuID = TableA.MenuID
> )
> Steve Kass
> Drew University
> Nancy Shelley wrote:
>|||Nancy,
I believe the syntax I suggested works for any length [text] or
[image] column.
You can also update or insert data into a text or image column if you
provide
it as a literal string or binary value. The 8000 byte limit does not
apply to
literals.
SK
Nancy Shelley wrote:

>Hi Steve:
>Thanks for the quick response. Supplying BLOB columns with text or image
>data that's less than or equal to 8000 bytes in size is as straightforward
>as updating any other type of column (you can use insert/update) but when
>values are larger than 8000 you need to use updatetext or writetext. This
>is what I was having trouble with - I know the syntax for updating my tabl
e
>the regular way I just wasn't sure about using updatetext or writetext.
>I ended up solving my own problem - my pointer was invalid. I was getting
>an invalid pointer reference to the text column.
>Thanks for your help!
>Nancy
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OqsmT2jWFHA.3488@.tk2msftngp13.phx.gbl...
>
>
>