SQL Server parse varchar string

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
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
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
 
Randy PooleCommented:
Why are you doing this?  What are you using for your back end development?
0
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
ZberteocCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.