On 11 Jan 2005 14:43:36 -0800, sysindex@gmail.com wrote:

>I am trying to find a way to dynamically retrieve the substring
>starting point of 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.

Hi sysindex,

First, you should know that the numbering for SUBSTRING starts at 1 in SQL
Server, not at 0.

Second, with your WHERE clause, the search string will always start on
position 1. I assume you meant to write WHERE DocText LIKE '%claim%' (note
the extra %).

SELECT ID, SUBSTRING (DocText, CHARINDEX('claim', DocText), 200)
FROM MyTable
WHERE DocText LIKE '%claim%'
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)