I've noticed that the CONTAINS function in the SQL SELECT statement has a
strange behavior on XML. When I use a statement like:
SELECT * FROM T where CONTAINS(dcxml,'pesca')
where "dcXML" is the column name and "pesca" is the word I'm searching for,
it only finds XML docs in the form of:
<tag1>
<key>...</key>
<key>...</key>
<key>... pesca ...</key>
</tag1>
(pesca always in the last <key> of the set)
and skips those like
<tag1>
<key>...</key>
<key>... pesca ...</key>
<key>...</key>
</tag1>
(pesca somewhere else in the repeated tag set)
Any help?
Are you indexing the xml as text or in an image column? If you are indexing
xml in an image column which word breaker are you using?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||MytyMyky,
Could you provide the full output from the below SQL script as this is
helpful in troubleshooting SQL FTS issues.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- May require setting advance sp_configure settings
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Thanks,
John
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I store
the xml in a xml column.
"Hilary Cotter" wrote:
> Are you indexing the xml as text or in an image column? If you are indexing
> xml in an image column which word breaker are you using?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> for,
>
>
|||I get the result in 11 tables:
--1--
us_english
--2--
Microsoft SQL Server Yukon - 9.00.852 (Intel X86) Jul 19 2004 22:09:12
Copyright (c) 1988-2003 Microsoft Corporation Beta Edition on Windows NT 5.2
(Build 3790: )
--3--
5 testeCatalog C:\fulltextcatalog\testeCatalog 0 1
--4--
dboTPK_T10testeCatalog
--5--
dbo2073058421TdcXml2NULLNULL2070
--6--
Tdbouser table2004-09-13 14:17:54.903
--7--
IDintno410 0 no(n/a)(n/a)NULL
dcXmlxmlno-1 no(n/a)(n/a)NULL
--8--
ID110
--9--
No rowguidcol column defined.
--10--
PK_Tclustered, unique, primary key located on PRIMARYID
--11--
PRIMARY KEY (clustered)PK_T(n/a)(n/a)(n/a)(n/a)ID
"John Kane" wrote:
> MytyMyky,
> Could you provide the full output from the below SQL script as this is
> helpful in troubleshooting SQL FTS issues.
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> -- May require setting advance sp_configure settings
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Thanks,
> John
|||Yes, that is somewhat crucial. Can we see an example of your query?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I
store[vbcol=seagreen]
> the xml in a xml column.
> "Hilary Cotter" wrote:
indexing[vbcol=seagreen]
has a[vbcol=seagreen]
|||I included an example in my first post:
select * from T where contains(dcXML,'something')
"Hilary Cotter" wrote:
> Yes, that is somewhat crucial. Can we see an example of your query?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> store
> indexing
> has a
>
>
Showing posts with label likeselect. Show all posts
Showing posts with label likeselect. Show all posts
Friday, February 24, 2012
Sunday, February 19, 2012
contact 2 column
Hi I have 2 coloumn one hast char(Productname) type another has int type (CustomerID)I want to contact them in my stored procedure like:
select Productname+CustomerID az newID
but I have error becouse one of them are int another in char ddo you have any idea?
I suggest that you read about "Datatype Precedence" in Books Online. It will explain what that fail. SQL
Server will try to convert the string to an int. You have to use an explicit CAST:
SELECR Productname + CAST(CustomerID AS varchar(20))
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type (CustomerID)I want to contact them
in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have any idea?
|||mahsa
CREATE TABLE #Test
(
col INT,
col1 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES (1,'AA')
INSERT INTO #Test VALUES (2,'BB')
GO
SELECT CAST(col AS VARCHAR(10))+col1 FROM #Test
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type
(CustomerID)I want to contact them in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have
any idea?
|||thank you it works
select Productname+CustomerID az newID
but I have error becouse one of them are int another in char ddo you have any idea?
I suggest that you read about "Datatype Precedence" in Books Online. It will explain what that fail. SQL
Server will try to convert the string to an int. You have to use an explicit CAST:
SELECR Productname + CAST(CustomerID AS varchar(20))
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type (CustomerID)I want to contact them
in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have any idea?
|||mahsa
CREATE TABLE #Test
(
col INT,
col1 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES (1,'AA')
INSERT INTO #Test VALUES (2,'BB')
GO
SELECT CAST(col AS VARCHAR(10))+col1 FROM #Test
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type
(CustomerID)I want to contact them in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have
any idea?
|||thank you it works
contact 2 column
Hi I have 2 coloumn one hast char(Productname) type another has int type (Cu
stomerID)I want to contact them in my stored procedure like:
select Productname+CustomerID az newID
but I have error becouse one of them are int another in char ddo you have an
y idea?I suggest that you read about "Datatype Precedence" in Books Online. It will
explain what that fail. SQL
Server will try to convert the string to an int. You have to use an explicit
CAST:
SELECR Productname + CAST(CustomerID AS varchar(20))
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type (Customer
ID)I want to contact them
in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have any idea
?|||mahsa
CREATE TABLE #Test
(
col INT,
col1 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES (1,'AA')
INSERT INTO #Test VALUES (2,'BB')
GO
SELECT CAST(col AS VARCHAR(10))+col1 FROM #Test
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type
(CustomerID)I want to contact them in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have
any idea?|||thank you it works
stomerID)I want to contact them in my stored procedure like:
select Productname+CustomerID az newID
but I have error becouse one of them are int another in char ddo you have an
y idea?I suggest that you read about "Datatype Precedence" in Books Online. It will
explain what that fail. SQL
Server will try to convert the string to an int. You have to use an explicit
CAST:
SELECR Productname + CAST(CustomerID AS varchar(20))
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type (Customer
ID)I want to contact them
in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have any idea
?|||mahsa
CREATE TABLE #Test
(
col INT,
col1 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES (1,'AA')
INSERT INTO #Test VALUES (2,'BB')
GO
SELECT CAST(col AS VARCHAR(10))+col1 FROM #Test
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type
(CustomerID)I want to contact them in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have
any idea?|||thank you it works
Subscribe to:
Posts (Atom)