searching for TAB and other ascii characters in a string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Faisal - 24 Aug 2006 19:07 GMT

Hi
does anyone know how to search for Tab or other ascii characters (like
carriage retrun, line feed, etc) within a string (either varchar, or
text). i'm using sql 2000..

Also i was looking for some resources on Full text causing blocking on
the server but can't find anything. a partial refresh of the full text
catalog causes massive blocking, however the catalog is not updated
that frequently. this one is in sql 2005. any ideas?

thanks

Reply to this Message

Hilary Cotter - 25 Aug 2006 11:19 GMT

use charindex(char(9), column) for this. SQL FTS does not index control
characters. Unfortunately sql fts does cause considerable blocking while the
population is occurring. You might want to set sp_full_service
'resource_usage', 2 to see if this help, it will make your population take
longer.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hi
> does anyone know how to search for Tab or other ascii characters (like
[quoted text clipped - 7 lines]
>
> thanks

Reply to this Message

Faisal - 26 Aug 2006 15:05 GMT

thanks Hilary

i'm relatively new in using Full Text, so i looked up the information
you had given and found some thing interesting.. i queried the
fulltextserviceproperty and i wanted to check if the settings are
fine..

SELECT    fulltextserviceproperty('ResourceUsage') as ResourceUsage,
        fulltextserviceproperty('ConnectTimeout') as ConnectTimeout,
        fulltextserviceproperty('DataTimeout') as DataTimeout

ResourceUsage ConnectTimeout DataTimeout
------------- -------------- -----------
3 0 0

ConnectTimeout and DataTimeout seems suspect to me. looking at 2005
booksonline, it says these two parameters are for backward
compatibility, but didn't say how their behavior is managed in 2005..
so i looked at 2000 booksonline and found that the values are supposed
to be between 1 and 32767 seconds (which is about 9 hours)... i also
found that the default is supposed to be 120 (but i'm not able to
validate that)..

do you think that these two settings (0's) could be leading to
blocking? i would imagine so, but i can't find any solid answers.. i
would appreciate your comments and with this, do you still think that
changing the resource_usage to 2 would help.. this is a production
server and i wanted to make sure i understand the impact of this before
changing the values. your help is really appreciated.

thanks

> use charindex(char(9), column) for this. SQL FTS does not index control

.... DELETED to avoid repetition (i hope everyone does that to keep the
posts short :)

Reply to this Message

Hilary Cotter - 27 Aug 2006 12:18 GMT

No, those settings are legacy settings for MSSearch. MSSearch was also the
engine used by SharePoint, Exchange, etc, and these settings are more
relevant to them. MSFTESQL is rewritten for SQL and these setting AFAIK
don't do anything.

Signature

> thanks Hilary
>
[quoted text clipped - 31 lines]
> .... DELETED to avoid repetition (i hope everyone does that to keep the
> posts short :)

Reply to this Message