TEXTPTR? PATINDEX? READTEXT?
Posted on 2006-03-26
Need advice on the "best-approach" to accomplish ... this ...
TEXT var types in a MS SQL 8.0 database.
My TEXT fields can contain (literally) up to 2 gb each.
Using TSQL in a Stored Proc, I need to
"walk" through all records meeting a condition
and for each record
"walk" through every instance of the words "Hello World" appearing anywhere in the TEXT column
and for each instance ...
capture the 500 characters BEFORE "Hello World" and the 500 characters AFTER "Hello World"
So, if a single record's 2gb TEXT field has 10 instances of "Hello World", then I need each instance's 500chars+"Hello World"+500chars
I assume I'll start with a #tempTable, and use a cursor to walk the records ...
Finding 10 instances, I'll write 500chars+"Hello World"+500chars to the temp table .... 10 times ....
But ... Using TSQL ... how the heck do I walk the 2gb of each TEXT field to find the 10 instances?????
Advice is welcome.
Speed will be important ...