Searching for specific text 1

[StatisticianDB]

I'm trying to write a sp which would search through a text datatype for a certain "event =" keyword. And when found, will return all number right after the "event =" keyword.
ex) If the text contains %"event = 123"%, the sp should go out and find "event = " and return "123". Any ideas on how I might go about this in sql?

 

[tlbroadbent]

You could do something similar to the following using the CHARINDEX function to locate the event in the text column.

Create Proc FindEvents @event varchar(20) As

Declare @strlen int
Set @strlen=len(@event)+1

Select
col1, col2, col3,
substring(col4,charindex(@event,col4)+@strlen,3) As EventNo
From EventTbl
Where TextCol Like '%' + @event + '%' Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[StatisticianDB]

Thank you tlbroadbent, your reply is bringing me down the right path. Unfortunately, when I tried it, I received an error because I was trying to use the substring function on a text datatype. The only datatype I can use with a substring function are char/varchar and binary/varbinary.
I tried converting the text datatype into a varchar but the string I am looking for extends beyond the 255 character limitation of varchar and gets cut off when I use convert. Any suggestions on this new issue?

 

[tlbroadbent]

Which version of SQL Server are you using? Substring can be used on TEXT columns in SQL 7 and 2000. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[StatisticianDB]

I'm using 6.5, again very unfortunate.

 

[tlbroadbent]

I don't have any SQL 6.5 documentation so I can't tell how to acomplish this in SQL 6.5. However, I think you could look into the READTEXT function. It should allow you to select the text you want.

SQL 2000 example - 25 characters of a text column:

USE pubs
GO

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'

READTEXT pub_info.pr_info @ptrval 1 25

GO Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[StatisticianDB]

Thanks for the info tlbroadbent, I can get the info from readtext and I'm now trying to put that output into a variable so I can do further manipulation with that data but I can't assign the output from readtext into anything. I read through the help files and bol but the documentations don't mention how to assign the output from readtext. Any suggestions.