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 BULK INSERT ASCII file into nvarchar column

Hi,

I have a problem with BULK INSERT. I created the following table:

Code Snippet

create table Test
(id char(4), name nvarchar(16), last char(1))

I am trying to bulk insert data from ASCII (not unicode) file with only two rows:

0011First name
0018Second name

Since it is a fixed length file, I am using the following format file:

Code Snippet

8.0
3
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "" 2 NAME HEBREW_CI_AS
3 SQLCHAR 0 0 "\r\n" 3 Last HEBREW_CI_AS

With bcp utility everything works just fine!

Code Snippet

bcp Demo.dbo.test in c:\test -T -f c:\test.fmt

But when I use BULK INSERT in the following form:

Code Snippet

BULK INSERT Test FROM 'c:\Test'
WITH
(
FORMATFILE='c:\Test.fmt',
CODEPAGE='OEM'
);

I am getting error

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 2 (name).

Now, one interesting thing: if I change the name field from nvarchar to varchar, it is working with BULK INSERT as well.

Can anybody explain what is going on here?

I am using MS SQL 2000 and MSDE

Thanks in advance,

Eugene.

Another thing is that if I set the format file to specify row delimiter for that nvarchar field, it will also work.

Code Snippet

8.0
2
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "\r\n" 2 NAME HEBREW_CI_AS

But then in the real system i can't have multiple fields within the file...

|||

On SQL2005 the problem does not exist! Then it seems like a bug in SQL2000!

sql

Problem with ampersand "&amp;"

I’m using XQUERY to slog through an XML doc, and dump the value of a CDATA
element into an NVARCHAR(MAX) field. The problem I’m running into is that
when an ampersand goes into that field, what I get back out in a query (using
FOR XML) is “&”.
using:
CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
The above stores a “&” in the field, even though the input was:
<![CDATA[&]>
Any insight is much appreciated.
Looks like my above post got mangled.
The value it stores is: "&"
And the value I get back is: "&amp;"
|||"Rob Epler" <RobEpler@.discussions.microsoft.com> wrote in message
news:1E572252-FD0D-4395-868F-504CA84D6C40@.microsoft.com...
> Looks like my above post got mangled.
> The value it stores is: "&"
> And the value I get back is: "&amp;"
This double escaping often happens when the source is already escaped but
marked as plain text, e.g. in a CDATA section. Can you show an example of
the source?

Joe Fawcett - XML MVP
http://joe.fawcett.name
|||Hello Joe,
The XMLRW parser seems to be discarding the CDATA markup around the value
when storing nodes like this. What Rob is storing is something like '<Value><![CDATA[&]]></Value>'.
In SQL Server 2005, at serialization time, all element text-nodes are entitized.
This seems to superceed what the spec seems to indicate should be done with
CDATA at parse time.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Hi Rob
sorry for the late reply.
But you should never extract values from an XML document using
CONVERT(nvarchar(MAX), T.c.query('data(Value)'))
use
T.c.value('Value', 'nvarchar(MAX)') instead. That should take care of your
encoding issue.
Best regards
Michael
"Rob Epler" <Rob Epler@.discussions.microsoft.com> wrote in message
news:BFFD350A-2B24-4133-8CEB-FCF5F700D560@.microsoft.com...
> I'm using XQUERY to slog through an XML doc, and dump the value of a CDATA
> element into an NVARCHAR(MAX) field. The problem I'm running into is that
> when an ampersand goes into that field, what I get back out in a query
> (using
> FOR XML) is "&".
> using:
> CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
> The above stores a "&" in the field, even though the input was:
> <![CDATA[&]>
> Any insight is much appreciated.
>

Problem with ampersand "&amp;"

I’m using XQUERY to slog through an XML doc, and dump the value of a CDATA
element into an NVARCHAR(MAX) field. The problem I’m running into is that
when an ampersand goes into that field, what I get back out in a query (usin
g
FOR XML) is “&”.
using:
CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
The above stores a “&” in the field, even though the input was:
<![CDATA[&]>
Any insight is much appreciated.Looks like my above post got mangled.
The value it stores is: "&"
And the value I get back is: "&"|||"Rob Epler" <RobEpler@.discussions.microsoft.com> wrote in message
news:1E572252-FD0D-4395-868F-504CA84D6C40@.microsoft.com...
> Looks like my above post got mangled.
> The value it stores is: "&"
> And the value I get back is: "&"
This double escaping often happens when the source is already escaped but
marked as plain text, e.g. in a CDATA section. Can you show an example of
the source?
Joe Fawcett - XML MVP
http://joe.fawcett.name|||Hello Joe,
The XMLRW parser seems to be discarding the CDATA markup around the value
when storing nodes like this. What Rob is storing is something like '<Value>
<![CDATA[&]]></Value>'.
In SQL Server 2005, at serialization time, all element text-nodes are entiti
zed.
This seems to superceed what the spec seems to indicate should be done with
CDATA at parse time.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/sql

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 while using sp_ExecuteSql

Hi all

I have a stored procedure as I placed below

create procedure ps_Select_Student
@.RollNo nvarchar(50),
@.Class int
AS
Begin
declare @.MainQuery nvarchar(4000)
set @.MainQuery = 'select StudentName where RollNo=@.RollNo and Class=@.Class'

exec sp_ExecuteSql @.MainQuery
End

Go

While executing this procedure in Query Analyser i am agetting an error that

must declare @.RollNo

What is the problem. Please help me out.

You have to declare those variables on param declaration parameter of sp_executesql

Code Snippet

create procedure ps_Select_Student

@.RollNo nvarchar(50),

@.Class int

AS

Begin

Declare @.MainQuery nvarchar(4000)

Declare @.ParamDecl nvarchar(4000)

set @.MainQuery = N'select StudentName where RollNo=@.RollNo and Class=@.Class'

set @.ParamDecl = N'@.RollNo nvarchar(50),@.Class int'

Exec sp_ExecuteSql @.MainQuery , @.ParamDecl, @.RollNo, @.Class

End

Go

problem using LIKE in UNICODE characters...

I have a table with nVarchar column.

If i do a search like this
----------------------
SELECT ID, Book, Chapter, Number, Amharic, English
FROM tbl_test
WHERE (Amharic LIKE '%???%')
----------------------

it doesn't return anything but if i add 'N' after LIKE as
----------------------
SELECT ID, Book, Chapter, Number, Amharic, English
FROM tbl_test
WHERE (Amharic LIKE N'%???%')
----------------------

It returns the whole table without filtering.

Can someone help me with this?

What about this:

WHERE RTRIM(Amharic) LIKE '%???%'