July 12, 2016 at 9:13 am
I´m trying to create FullText Index on Image field witch contains text in rich text format. I´m from Iceland and we use several characters based on our language like á, í, ó, ú, ð, þ and æ. When I´m searching for those letters in words using this Index I don´t get any results, although I know they are in text. But words without these Icelandic characters are easy to find.
That´s how I created the Index:
USE LOKE64
GO
CREATE FULLTEXT INDEX ON MalBokunSkyrsla KEY INDEX MalBokunSkyrsla_PK ON (Loke64_catalog) WITH (CHANGE_TRACKING AUTO)
GO
USE LOKE64
GO
ALTER FULLTEXT INDEX ON MalBokunSkyrsla ADD (Skjalid TYPE COLUMN Filextension LANGUAGE 1039)
GO
and I´m serching like this:
declare @Leit nvarchar(100)
set @Leit = N'"áður"'
Select Malsnumer, msk.Skyrslunumer, @Leit as Leitarord
from MalBokunSkyrsla mbs join MalSkyrsla msk on mbs.fkMalSkyrsla_ID = msk.MalSkyrsla_ID
join MalMalaskra mal on msk.fkMalMalaskra_ID = mal.MalMalaskra_ID
where CONTAINS(Skjalid,@Leit, LANGUAGE 1039)
What am I missing?
July 12, 2016 at 10:47 am
torfi (7/12/2016)
I´m trying to create FullText Index on Image field witch contains text in rich text format. I´m from Iceland and we use several characters based on our language like á, í, ó, ú, ð, þ and æ. When I´m searching for those letters in words using this Index I don´t get any results, although I know they are in text. But words without these Icelandic characters are easy to find.That´s how I created the Index:
USE LOKE64
GO
CREATE FULLTEXT INDEX ON MalBokunSkyrsla KEY INDEX MalBokunSkyrsla_PK ON (Loke64_catalog) WITH (CHANGE_TRACKING AUTO)
GO
USE LOKE64
GO
ALTER FULLTEXT INDEX ON MalBokunSkyrsla ADD (Skjalid TYPE COLUMN Filextension LANGUAGE 1039)
GO
and I´m serching like this:
declare @Leit nvarchar(100)
set @Leit = N'"áður"'
Select Malsnumer, msk.Skyrslunumer, @Leit as Leitarord
from MalBokunSkyrsla mbs join MalSkyrsla msk on mbs.fkMalSkyrsla_ID = msk.MalSkyrsla_ID
join MalMalaskra mal on msk.fkMalMalaskra_ID = mal.MalMalaskra_ID
where CONTAINS(Skjalid,@Leit, LANGUAGE 1039)
What am I missing?
Sæll Torfi,
and welcome to the forum. My suggestion is to specify the language (1039) when you create the FULLTEXT INDEX, not when adding the "Skjalid" column. IIRC I've never had any such problems with FTI/S
😎
July 12, 2016 at 10:49 am
Quick question, why are you using IMAGE data type and not NVARCHAR?
😎
July 12, 2016 at 1:33 pm
NVARCHAR won't hold RTF files, just the text. I would expect VARBINARY instead of IMAGE, for RTF files.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 12, 2016 at 2:46 pm
GSquared (7/12/2016)
NVARCHAR won't hold RTF files, just the text. I would expect VARBINARY instead of IMAGE, for RTF files.
This is wrong, NVARCHAR and VARCHAR will hold RTF files just fine.
😎
Save the attached file as C:\TEMP\RTFTEST001.rtf and run this code
USE tempdb;
GO
IF OBJECT_ID(N'dbo.TBL_RTF_DOC') IS NOT NULL DROP TABLE dbo.TBL_RTF_DOC;
CREATE TABLE dbo.TBL_RTF_DOC
( RTF_DOC_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,RTF_DOCUMENT NVARCHAR(MAX) NOT NULL
);
GO
INSERT INTO dbo.TBL_RTF_DOC(RTF_DOCUMENT)
SELECT
X.RTF_DATA
FROM
(
SELECT
CONVERT(NVARCHAR(MAX), BulkColumn , 1 ) AS RTF_DATA
FROM OPENROWSET(
BULK 'C:\TEMP\RTFTEST001.rtf',
SINGLE_CLOB) AS X
) AS X
SELECT
RTF_DOC_ID
,DATALENGTH(RTF_DOCUMENT)
,RTF_DOCUMENT
FROM dbo.TBL_RTF_DOC;
July 13, 2016 at 4:40 am
Thanks for the advise put it didn't change anything. Now I created the index like this:
CREATE FULLTEXT INDEX ON MalBokunSkyrsla
( Skjalid TYPE COLUMN Filextension Language 1039) KEY INDEX MalBokunSkyrsla_PK
ON Loke64_catalog;
July 13, 2016 at 4:56 am
torfi (7/13/2016)
Thanks for the advise put it didn't change anything. Now I created the index like this:CREATE FULLTEXT INDEX ON MalBokunSkyrsla
( Skjalid TYPE COLUMN Filextension Language 1039) KEY INDEX MalBokunSkyrsla_PK
ON Loke64_catalog;
Torfi,
try turning off the Accent Sensitivity
😎
alter fulltext catalog Loke64_catalog
REBUILD WITH ACCENT_SENSITIVITY=OFF;
July 13, 2016 at 7:38 am
Eirikur Eiriksson (7/12/2016)
GSquared (7/12/2016)
NVARCHAR won't hold RTF files, just the text. I would expect VARBINARY instead of IMAGE, for RTF files.This is wrong, NVARCHAR and VARCHAR will hold RTF files just fine.
😎
Save the attached file as C:\TEMP\RTFTEST001.rtf and run this code
USE tempdb;GO
IF OBJECT_ID(N'dbo.TBL_RTF_DOC') IS NOT NULL DROP TABLE dbo.TBL_RTF_DOC;
CREATE TABLE dbo.TBL_RTF_DOC
( RTF_DOC_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,RTF_DOCUMENT NVARCHAR(MAX) NOT NULL
);
GO
INSERT INTO dbo.TBL_RTF_DOC(RTF_DOCUMENT)
SELECT
X.RTF_DATA
FROM
(
SELECT
CONVERT(NVARCHAR(MAX), BulkColumn , 1 ) AS RTF_DATA
FROM OPENROWSET(
BULK 'C:\TEMP\RTFTEST001.rtf',
SINGLE_CLOB) AS X
) AS X
SELECT
RTF_DOC_ID
,DATALENGTH(RTF_DOCUMENT)
,RTF_DOCUMENT
FROM dbo.TBL_RTF_DOC;
Sure, that'll work, so long as you don't mind losing your extended character-set data (which wouldn't work for the purposes of this thread, since he needs Unicode characters), and so long as you want your data to look like:
{\rtlch\fcs1 \af31507 \ltrch\fcs0 \b\insrsid16138788\charrsid16138788 This is bold text!
If you store an RTF document as a file-system VarBinary, you can still use it as a document, and you can index, etc., the whole thing. That's what I'm talking about.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 13, 2016 at 8:33 am
Takk, takk Eirikur. This was a great relief. I've been struggling with this problem nearly a week :w00t:
July 13, 2016 at 11:13 am
torfi (7/13/2016)
Takk, takk Eirikur. This was a great relief. I've been struggling with this problem nearly a week :w00t:
Ekkert mal
😎
You are welcome
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply