SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 05:33 AM
| ||||
| ||||
 SUBSTRING question 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.      |
|
02-29-2008, 05:33 AM
| ||||
| ||||
| Re: SUBSTRING question 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 |
Linear Mode
