Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query - Removing unwanted Results in field

Posted on 2013-01-17
4
Medium Priority
?
386 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 668 total points
ID: 38786588
0
 
LVL 4

Assisted Solution

by:krtyknmsql
krtyknmsql earned 668 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 664 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

596 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