Larry Brister
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|3307 6,Email|lr brister@my dev.com|lr brister@yo urldev.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
LastName|HIN|HaHa,ZipCode|
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
Why are you doing this? What are you using for your back end development?
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.
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,',')
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?
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?
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.