truncating text

Question

Post reply

truncating text

Magy

SSCarpal Tunnel

Points: 4217
More actions
February 15, 2010 at 2:46 pm

#137288

I'm currently searching a table in my database using a freetextfield like this:
SELECT 'Page Text'AS RESULTCATEGORY,
CONVERT(varchar(255), td.[text])AS RESULTTEXT
FROM(FREETEXTTABLE(textData, text, @searchstring) ftt
INNER JOIN textData td
ON ftt. = td.textID)
This search works, but it returns the entire block of text as the result of the search. I was hoping to only include 50 characters before the search term and 50 characters after the search term....that way the result will show the text surrounding the search term rather than just a huge block of text.
Is this possible with t-sql?
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

You may consider using a combination of substring, left and right string functions.

http://msdn.microsoft.com/en-us/library/ms181984.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004286 More actions · Answer 2

I'm not sure how you'd use substring in conjunction with something that doesn't return a starting position for the found string.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Within the query as is - I can't either. However, I was thinking something more along the lines of dumping that data into a temp table and then using those functions. I'm not sure if that will work too well - was hoping it was worth a shot.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 4

As Jeff says, a full-text search does not return any information concerning where the match was found. If the search term is known to be very simple (e.g. an exact word or phrase match, prefix search) you can use CHARINDEX or PATINDEX to determine a starting point. If you are doing stuff with inflectional searches, it becomes more difficult.

I saw a technique based on a CLR routine in a full-text search book once, but I don't recall the details - sorry about that.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 5

It's just a simple search, like one or 2 search terms...for example "race car", or "gasoline", "volcano", "space shuttle", etc....nothing fancy like using ANDs, ORs, etc...

Thanks!

Paul White SSC Guru Points: 150467 More actions · Answer 6

Good. I hope it stays that way!

Post back if you need further assistance with this.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi