Solved

SQL Query - Removing unwanted Results in field

Posted on 2013-01-17
4
383 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 43

Accepted Solution

by:
Eugene Z 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 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