Solved

SQL Server parse varchar string

Posted on 2016-08-25
5
29 Views
Last Modified: 2016-08-25
I need to parse a string that comes in to me in this format
LastName|HIN|HaHa,ZipCode|33067|33076,Email|lrbrister@mydev.com|lrbrister@yourldev.com
Essentially what this is ...
 What the lastname was and is now
What the Zipcode was and is now
What the email was and is now
etc...
But the column names change
What does not change is that the first character is a column followed by | which is what is was followed by a second | which is what it is now
Followed by a comma which sets the new record

I want the output to be
LastName from Hin to HaHa||ZipCOde from 33067 to 33076||Email from .... to .... etc
0
Comment
Question by:lrbrister
[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
  • 2
  • 2
5 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 41770544
Why are you doing this?  What are you using for your back end development?
0
 

Author Comment

by:lrbrister
ID: 41770550
Randy,
Not being rude... you have helped me many times over the year... but not sure why that pertains to my question.

In any case I have found a solution.

DECLARE @str nvarchar(max)
SET @str = 'LastName|HIN|HaHa,ZipCode|33067|33076,Email|lrbrister@mydev.com|lrbrister@liveldev.com,,,,'
SET @str = REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(@str, ',', ' '))), ' ', ','),',,',',')  


SELECT REPLACE(STUFF([str], CHARINDEX('|', [str]), 1, ' FROM '),'|', ' TO ') [str] 
FROM [dbo].[ParseList](@str,',')

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41770616
That code returns something else than you stated in the question:

str
-------------------------------------------------------------------------------------------
LastName FROM HIN TO HaHa
ZipCode FROM 33067 TO 33076
Email FROM lrbrister@mydev.com TO lrbrister@liveldev.com

Is that what you want?
0
 

Author Comment

by:lrbrister
ID: 41770621
Zberteoc,
I did not place all of the code in my example.

I set it further down the line to get back the full string with || separator.

My apologies
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 41770625
I thought you wanted this:
DECLARE @str nvarchar(max),@str2 nvarchar(max)=''
SET @str = 'LastName|HIN|HaHa,ZipCode|33067|33076,Email|lrbrister@mydev.com|lrbrister@liveldev.com,,,,'
SET @str = REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(@str, ',', ' '))), ' ', ','),',,',',')  

SELECT 
    @str2=@str2+[str]+'||'
FROM
(
    SELECT 
	   REPLACE(STUFF(elem, CHARINDEX('|', elem), 1, ' FROM '),'|', ' TO ') [str] 
    FROM [dbo].[ParseList](@str,',')
)q

---------------------------------------------------------------------------------------------------------------------------------------------------
LastName FROM HIN TO HaHa||ZipCode FROM 33067 TO 33076||Email FROM lrbrister@mydev.com TO lrbrister@liveldev.com||

SELECT @str2

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

730 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