Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

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
Avatar of Randy Poole
Randy Poole
Flag of United States of America image

Why are you doing this?  What are you using for your back end development?
Avatar of Larry Brister

ASKER

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

Avatar of Zberteoc
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?
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
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial