[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to parse data in the middle of the value returned

Posted on 2007-08-08
4
Medium Priority
?
189 Views
Last Modified: 2013-11-05
In SQL Server 2005 Reporting Services I have a queried field that has a value like this:
Customer=customername@company.com|PurchasePlan=12345|License=licensenumber

I need the value returned to only be the 'customername@company.com' part. In other words just the part after 'Customer=' and before the'|PurchasePlan=' parts These values are always included in the data.
0
Comment
Question by:brooksmato
  • 2
2 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 19660154
If the format is consistent (i.e. always in that sequence):

DECLARE @Str varchar(1000)
SELECT @Str = 'Customer=customername@company.com|PurchasePlan=12345|License=licensenumber'
SELECT SUBSTRING(@Str,10,CHARINDEX('|',@Str,10)-10)
0
 
LVL 29

Accepted Solution

by:
Nightman earned 2000 total points
ID: 19660163
this should work if the format is not consistent:

DECLARE @Str varchar(1000)
SELECT @Str = 'Customer=customername@company.com|PurchasePlan=12345|License=licensenumber'
SELECT
  SUBSTRING(@Str, CHARINDEX('Customer=', @Str) + 9,
    CASE WHEN CHARINDEX('|', @Str, CHARINDEX('Customer=', @Str)) = 0 THEN LEN(@Str)
      ELSE CHARINDEX('|', @Str, CHARINDEX('Customer=', @Str)) - (CHARINDEX('Customer=', @Str) + 9)  
    END)
AS [Customer]
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

872 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