Gazi Mahmud (sysindex@gmail.com) writes:
> I am trying to find a way to dynamically retrieve the substring
> starting point for an nText field.
>
>
> My query looks something like
>
> SELECT ID,Substring(DOCTEXT,0,200) from mytable where DOCTEXT like
> 'claim%'"
>
> This query has substring starting point set to 0. Is there a way to
> determine the starting point based on the first occurrence of the
> search term? If first occurrence of 'claim' is at 25th character then
> the query should start substring at 25th and end at 225th character. I
> am trying to avoid usage of UDF. Is there a way to handle this through
> plain a SQL statement? Any help would be appreciated. TIA.

With charindex() you can specify a starting point for the search. But
there are two problems:

o charindex searches for a fixed string, not a pattern.
o charindex does not handle ntext beyond the 4000th charactter.

I believe that patindex is said to support ntext, but I don't know
if it does in practice. And in any case, patindex() does not provide
any starting point for the parameter.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp