Create Fulltext index on decimal/number coloumn, possible????? (SQL Server 2008)

Question

I'd like to create a fulltext index on several columns which are varchars and numbers. And there is the problem with the numbers.

Is there really no way to add column as number or decimal for the fulltext index?

I've got for example a table article:

CREATE TABLE article
(   articleid   number(10)  not null,
    articlename     varchar(50)     not null,
    articlecode1    varchar(50)
);

CREATE FULLTEXT CATALOG MyCatalog;

CREATE FULLTEXT INDEX ON artikel
     (articleid LANGUAGE 0x0,
      articlename LANGUAGE 0x0,
      articlecode1 LANGUAGE 0x0)
KEY INDEX PK_ARTICLE ON MyCatalog
WITH CHANGE_TRACKING AUTO;

It couldn't be created, because articleid is a number!

My alternate solution is to create a second column such as articleidtext varchar as a copy of articleid. Afterward to fill up with a trigger.

Is there really no other way?

I've tried also to Cast like: CAST(articleid AS varchar) LANGUAGE 0x0 it doesn't work either.

any other suggestion??

thx...

If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Jan 28 '11 at 13:23

score 2 · Answer 1 · answered Jan 28 '11 at 13:27

2

You can create a computed, persisted column of type VARCHAR based on your articleId - this doesn't need to be filled with a trigger or anything, it will always represent the articleId, but as a VARCHAR column, and thus it can be fulltext indexed:

ALTER TABLE dbo.article
ADD ArticleIDText AS CAST(ArticleID AS VARCHAR(20)) PERSISTED

Now put your fulltext index on ArticleIDText and everything should be just fine....

answered Jan 28 '11 at 13:27

marc_s

732,580
175
1,330
1,459

Perfect, Thank you. Didn't thought about that, this is a good alternative to trigger. cheers robert – robert Jan 28 '11 at 13:35

score 0 · Answer 2 · answered Jul 13 '15 at 15:56

0

fulltext works only with types :

char, varchar, nchar, nvarchar, text, ntext, image, xml или varbinary(max)

answered Jul 13 '15 at 15:56

zloctb

10,592
8
70
89

score 0 · Answer 3 · answered Jan 28 '11 at 13:05

0

You cannot create Fulltext indexes on columns which are numbers. If you want to be able to search it using full text, u can convert the column to a string.

But the real question is are you looking for full text index or just a normal index!?

answered Jan 28 '11 at 13:05

Whimsical

5,985
1
31
39

Create Fulltext index on decimal/number coloumn, possible????? (SQL Server 2008)

3 Answers3