How to evaluate one line of a memo field 1

[Solojer]

Hi there!

I need to select records based on a value in the 4th or 5th line of a memo field. Could someone suggest an easy way to do this? Thanks!

 

[djj55]

When you say "memo field", what do you mean? Is it TEXT or VARCHAR(MAX) or...?
What version of SQL Server are you on?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Solojer]

Sorry about that.. obviously more detail would be helpful.

The memo field is varchar(max).

SQL Server 2005.

Thanks

 

[simian336]

might want to post a data example and what you are searching for.

Simi

 

[Solojer]

Sure..

It's in field that contains the text of an e-mail.

I need to identify those e-mails that have the text "***Please reply within five days***" in the subject field of the e-mail.

I need to exclude replies sent to this original e-mail, which is why it's important to ensure the string I'm looking for appears only in the first few lines of the field (the text would stil be in a reply, but further down...)

Thanks

 

[gmmastros]

I would suggest that you find a "split" function. Currently, all of your data is stored in a single column of a single row. A split function will allow you to split the data in to multiple rows, allowing you to examine and process each line separately.


For example:

CREATE Function [dbo].[Split](@CommaDelimitedFieldNames Varchar(Max), @CharToFind VarChar(10) ) 
Returns @Tbl_FieldNames Table (Position Integer Identity(1,1), FieldName VarChar(1000)) As 
	Begin 

		Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @CharToFind
		Declare @Pos1 Int
		Declare @pos2 Int

		Set @Pos1=1
		Set @Pos2=1
		While @Pos1<Len(@CommaDelimitedFieldNames)
			Begin
				Set @Pos1 = CharIndex(@CharToFind, @CommaDelimitedFieldNames,@Pos1)
				Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(100))
				Set @Pos2=@Pos1+len(@CharToFind)
				Set @Pos1 = @Pos1+Len(@CharToFind)
			End 
		Return
	End

You only need to run the code shown above once to create the function.

You would use it like this:

Declare @Temp Table(Id Int, Data Varchar(max))

Insert Into @Temp Values(1, 'Line 1' + Char(13) + Char(10) 
                            + 'Line 2' + Char(13) + Char(10) 
                            + 'Line 3' + Char(13) + Char(10) 
                            + 'Line 4' + Char(13) + Char(10) 
                            + 'Line 5')
Insert Into @Temp Values(2, 'Line A' + Char(13) + Char(10) 
                            + 'Line B' + Char(13) + Char(10) 
                            + 'Line C' + Char(13) + Char(10) 
                            + 'Line D' + Char(13) + Char(10) 
                            + 'Line E')

Select  *
From    @Temp T
        Cross Apply dbo.Split(T.Data, Char(13) + Char(10)) As Data
Where	T.Id = 2
        And Data.Position = 3

Notice that there is a WHERE clause on the query. T.ID = 2 limits the data to the row in @Temp = 2 (this is the one with Line A, Line B, etc...)

There is another where clause for Data.Position = 3. This limits the results to the 3rd line (which is 'Line C').

If you remove the where clause criteria for 'Data.Position = 3', you will see a row in the output for each line in the data.

If you remove the where clause criteria for T.Id = 2 (but leave Data.Position = 3), you will see the 3rd line for each row in @Temp.

I suggest you create the function I show in the first code block. Then copy/paste the code in the second code block to a new query editor window and play around with it some. Please make sure that you understand how it works and also how to manipulate the data. Once you understand it, you should be able to easily adapt it to your situation (using your table instead of @Temp).

Hope this helps.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tcsbiz]

Nice tip George. I just happen to have a use for this now. Here's a star.

Thanks.