Solved

SQL Query - Removing unwanted Results in field

Posted on 2013-01-17
4
378 Views
Last Modified: 2013-01-30
Hi all,

I have a simple SQL Query (I am using it to test results on a MS Lync 2010 Archiving server).

Code is:

SELECT Messages.MessageIdTime, Users.UserUri, Messages.Body
 FROM Messages
 INNER JOIN Users
 ON Messages.FromId=Users.UserId Or Messages.ToId = Users.UserId
	--INNER JOIN Users
	--	On Messages.ToId=Users.UserId
 Where Users.UserUri = @Username
 ORDER BY Messages.MessageIdTime

Open in new window


This is all fine but the messages.body field returns results that 95% of the time have the below as the result - all I am interested in is the part where I have written MESSAGE BODY this is the actual archived message.

<DIV style="font-size: 10pt;font-family: 'Segoe UI';color: #000000;direction: ltr;font-weight: normal;font-style: normal;text-decoration: none">[u]MESSAGE BODY[/u]</DIV>

Open in new window


I cannot use the "substring" command because this test does not always precede the message.  Is there any way I can only return results after the ">[u for example?

Cheers
K
0
Comment
Question by:techmiss
4 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ earned 167 total points
ID: 38786588
0
 
LVL 4

Assisted Solution

by:krtyknmsql
krtyknmsql earned 167 total points
ID: 38786692
Try the below query
SELECT Messages.MessageIdTime, Users.UserUri, SUBSTRING(Messages.Body,CHARINDEX('>[u]',Messages.Body) + 1,(CHARINDEX('[/u]',Messages.Body) - 1)) AS Body
 FROM Messages
 INNER JOIN Users
 ON Messages.FromId=Users.UserId Or Messages.ToId = Users.UserId
	--INNER JOIN Users
	--	On Messages.ToId=Users.UserId
 Where Users.UserUri = @Username
 ORDER BY Messages.MessageIdTime

Open in new window

0
 
LVL 4

Assisted Solution

by:mcmahon_s
mcmahon_s earned 166 total points
ID: 38786788
Create a function like so:

CREATE FUNCTION GetBody(@sourceText AS varchar(max)) RETURNS varchar(max)
AS
BEGIN
	DECLARE @StartPos int=0, @EndPos int=0

	SELECT @StartPos=CHARINDEX('">[u]', @sourceText,0)+5
	IF @StartPos > 5
		SET @EndPos=CHARINDEX('[/u]</DIV>', @sourceText,0)-1
	ELSE
		BEGIN
			SET @StartPos=1
			SET @EndPos=LEN(@sourceText)
		END
	RETURN (SUBSTRING(@sourceText,@StartPos,@EndPos-@StartPos+1))
END

Open in new window


Then call the function in your query:

SELECT Messages.MessageIdTime, Users.UserUri, dbo.GetBody(Messages.Body) AS Body
 FROM Messages
 INNER JOIN Users
 ON Messages.FromId=Users.UserId Or Messages.ToId = Users.UserId
	--INNER JOIN Users
	--	On Messages.ToId=Users.UserId
 Where Users.UserUri = @Username
 ORDER BY Messages.MessageIdTime

Open in new window

0
 

Author Closing Comment

by:techmiss
ID: 38835920
All the answers helped me understand this issue in more detail and reslove in a different way -hence the split points.

Thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now