Is this possible? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Is this possible?

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-12-13 : 06:02:17

I use the LIKE statment to search through a varchar field - Description - with length of max 8000 characters.

Is it possible to return only the part of the description that contains the searched for string. Let's say from 100 characters before the searched for string, to 100 characters after the searched for string.

E.g. WHERE Description LIKE '%middle%'
should return '.... this is middle text of a long description ...'.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-13 : 06:13:37

like:
select substring(description,patindex('%middle%',description)-100,patindex('%middle%,description)+100)
from mytable
where patindex('%middle%',description)>0

don't know if this will work

--------------------
keeping it simple...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-13 : 06:30:37

s.b.
substring(description,patindex('%middle%',description)-100,len('middle') + 200)

will need to deal with the string being in the first 100 chars if you don't want more than 100 trailing chars.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adlo
Posting Yak Master

108 Posts

Posted - 2004-12-13 : 09:43:15

Thanks.

There seems to be some problems when their is less than 100 characters to the left of 'middle'. When this happens then that amount is subtracted from the characters that will be displayed on the right.

Here is an example

DECLARE @Description varchar(8000)
DECLARE @SearchText varchar(100)
SET @Description='1234567890.'
SET @SearchText='5'
SELECT
substring(@Description,patindex('%'+@SearchText+'%',@Description)-3,patindex('%'+@SearchText+'%',@Description)+3)

returns '23456789'

DECLARE @Description varchar(8000)
DECLARE @SearchText varchar(100)
SET @Description='1234567890.'
SET @SearchText='2'
SELECT
substring(@Description,patindex('%'+@SearchText+'%',@Description)-3,patindex('%'+@SearchText+'%',@Description)+3)

returns '123'

adlo
Posting Yak Master

108 Posts

Posted - 2004-12-13 : 09:43:59

quote:
Originally posted by adlo

Thanks.

There seems to be some problems when there is less than 100 characters to the left of 'middle'. When this happens then that amount is subtracted from the characters that will be displayed on the right.

Here is an example

DECLARE @Description varchar(8000)
DECLARE @SearchText varchar(100)
SET @Description='1234567890.'
SET @SearchText='5'
SELECT
substring(@Description,patindex('%'+@SearchText+'%',@Description)-3,patindex('%'+@SearchText+'%',@Description)+3)

returns '23456789'

DECLARE @Description varchar(8000)
DECLARE @SearchText varchar(100)
SET @Description='1234567890.'
SET @SearchText='2'
SELECT
substring(@Description,patindex('%'+@SearchText+'%',@Description)-3,patindex('%'+@SearchText+'%',@Description)+3)

returns '123'

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-13 : 10:26:45

The code I posted should fix that problem but read the last sentence about another possible problem

DECLARE @Description varchar(8000)
DECLARE @SearchText varchar(100)
SET @Description='1234567890.'
SET @SearchText='5'
SELECT
substring(@Description,patindex('%'+@SearchText+'%',@Description)-3,len(@SearchText)+6)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources