Special escape characters in CONTAINSTABLE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Special escape characters in CONTAINSTABLE

Author

Topic

pjbmat1
Starting Member

1 Post

Posted - 2005-08-14 : 21:21:11

hi there,

New to the forums so apologise if this is in the wrong place.

I have a strange one. We have a column that is being full text catalogued and has data like:

(0-79 HP)

in it.

Now when we do a search for it we create a keywords variable with the followingn which is passed to CONTAINSTABLE:

"0-79*" AND "HP*"

It seems to me that the "0-" is in someway a special character? Can anyone confirm this? If I pass "79*" AND "HP*" it all works fine. What is special about "0-" when passed to CONTAINSTABLE and should it be replaced with some other character?

Cheers
Paul

sbarath
Starting Member

2 Posts

Posted - 2010-10-27 : 18:42:30

Hi,

I have a similar problem with escaping characters in a contains table.
In my case it is the & sign. In my database, I have a record with name A&B and when I am trying to search the same by specifying A&B in the query it does not return anything as the & is treated as logical and and not a part of search string. Please let me know if you found a solution to your problem.

Thanks,

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-27 : 23:17:06

quote:
Originally posted by sbarath

Hi,

I have a similar problem with escaping characters in a contains table.
In my case it is the & sign. In my database, I have a record with name A&B and when I am trying to search the same by specifying A&B in the query it does not return anything as the & is treated as logical and and not a part of search string. Please let me know if you found a solution to your problem.

Thanks,

Hi sbarath,

You should start a new thread and should not hijack very old thread for posting your question.

Now for the question posted by you please go through the below example and let us know whether it gives you an idea on how to handle special character.

Declare @Test table
(SValue varchar(50))

Insert into @Test
Select 'A&B' union
Select 'ABCL' union
Select 'All & is well'

Select * from @Test where svalue like '%[&]%'

Select * from @Test where svalue like 'A&B'

Select * from @Test where svalue like 'A[&]B'

sbarath
Starting Member

2 Posts

Posted - 2010-10-29 : 12:55:45

I think I was not clear in my question. I need to escape in containstable.

For e.g my query is like:

select * from
mytable,
CONTAINSTABLE (mytable,(c1, c2), 'A&B', LANGUAGE 'English') K

Assume mytable contains the value 'A&B' in columns c1 and c2
Theoritically the search query mentioned above should return all the rows containing 'A&B' in columns c1 and c2.
Actually the search does not return anything because here the & is not treated as a literal character. It is treated as A and B.

Hence I would like to escape the '&' before using it as a search string in the containstable.
Hope I am clear now.

Many Thanks in Advance,
sbarath

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-30 : 03:48:23

Just a guess:

Try:

'A&&B'
'"A&B"'

Kristen
Test

22859 Posts

Posted - 2010-10-30 : 04:42:51

Punctuation not stored in the Free Text Index is it? If I've got that right the you cannot explicitly find "&", and "A&B" will be stored as two words, the same as "A B" - thus you may be able to find 'A B' or match the phrase with '"A B"'. Untested though, and CONTAINS is not something I know much about.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources