HOWTO Make an sp to extract text from text

12 Aug 2005 12:17 PM

ATS

HOWTO Make an sp to extract text from text

Please help,

I need a stored procedure (sp) or function to take in a "text" parameter,
and then find and return a section of the "text" parameter as another "text".

Example:

declare @csMyTextWithData text
declare @csTheSectionImLookingFor text

select @csMyTextWithData = '~~~~~ some HUGE data with my stuff inside it ~~~~'
select @csTheSectionImLookingFor = .dbo.ExtractText(@csMyTextWithData)

I do understand that sp do not let one "set" text data type in the
"conventional" sense, and that is OK. I'd settle for a temporary table, just
so long as it is GENERIC, where the "ExtractText" does not have to know
anything about the TEMP table in advance.

Any ideas?

12 Aug 2005 1:02 PM

Rick Sawtell

Show quote

"ATS" <A**@discussions.microsoft.com> wrote in message
news:E3811396-39B0-4C47-81D4-B00CC4CE4920@microsoft.com...
> HOWTO Make an sp to extract text from text
>
> Please help,
>
> I need a stored procedure (sp) or function to take in a "text" parameter,
> and then find and return a section of the "text" parameter as another
> "text".
>
> Example:
>
> declare @csMyTextWithData text
> declare @csTheSectionImLookingFor text
>
> select @csMyTextWithData = '~~~~~ some HUGE data with my stuff inside it
> ~~~~'
> select @csTheSectionImLookingFor = .dbo.ExtractText(@csMyTextWithData)
>
> I do understand that sp do not let one "set" text data type in the
> "conventional" sense, and that is OK. I'd settle for a temporary table,
> just
> so long as it is GENERIC, where the "ExtractText" does not have to know
> anything about the TEMP table in advance.
>
> Any ideas?

I'm not sure I understand what you want?

What do you want the procedure to return?
If you are passing in the text data, and what you are looking for in the
text data, then all you really need to do is verify whether or not that text
that you are looking for is in the text you passed in. If it is, you can
return true, or you can return the text you are looking for. You should be
able to do something with the PATINDEX function. If that doesn't work for
you, you could drop the data in a temp table and use a LIKE command.

HTH

Rick Sawtell
MCT, MCSD, MCDBA

12 Aug 2005 2:31 PM

Adam Machanic

Why can't you do this in the application tier?

--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

Show quote

"ATS" <A**@discussions.microsoft.com> wrote in message
news:E3811396-39B0-4C47-81D4-B00CC4CE4920@microsoft.com...
> HOWTO Make an sp to extract text from text
>
> Please help,
>
> I need a stored procedure (sp) or function to take in a "text" parameter,
> and then find and return a section of the "text" parameter as another
"text".
>
> Example:
>
> declare @csMyTextWithData text
> declare @csTheSectionImLookingFor text
>
> select @csMyTextWithData = '~~~~~ some HUGE data with my stuff inside it
~~~~'
> select @csTheSectionImLookingFor = .dbo.ExtractText(@csMyTextWithData)
>
> I do understand that sp do not let one "set" text data type in the
> "conventional" sense, and that is OK. I'd settle for a temporary table,
just
> so long as it is GENERIC, where the "ExtractText" does not have to know
> anything about the TEMP table in advance.
>
> Any ideas?