By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,415 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,415 IT Pros & Developers. It's quick & easy.

tsql Substring question

P: n/a
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.

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 11 Jan 2005 14:43:36 -0800, sy******@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)
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.