String manupulation

[Fatih235]

Hi all;

I have a column called news. In every row of this column there is a news approximately 150 chars long. Each of these rows contains the word "exactly". I want to select 20 characters before "exactly", the word "exactly" and 20 characters after "exactly" . Any suggestions?

 

[MeanGreen]

Try this:

select substring(news,charindex('exactly',news)-20,47) from table

Hope this helps

 

[mukund]

Select Substring(news, (Charindex('exactly', news, 1)) - 20, (Charindex('exactly', news, 1)) + len('exactly') + 20) From TableName

 

[rt63]

Just to suggest a scenario which may be possible. Word exactly may come within first 20 characters in news column. So just to make it a little more generic


select substring(news,charindex('exactly',news) -
case when charindex('exactly',news)> 20 then 20 else charindex('exactly',news) - 1 end
, case when charindex('exactly',news)> 20 then 20 else charindex('exactly',news) - 1 end + 27)
from table
where charindex('exactly',news) > 0

RT