Solved

SQL Query - Removing unwanted Results in field

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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