Solved

SQL Query - Removing unwanted Results in field

Posted on 2013-01-17
4
381 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL trigger 5 25
Acces SQL Insert Problem 6 33
sql 2016 data tools breakdown.. 1 15
MS SQL GROUP BY 5 7
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

820 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