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

x
?
Solved

SQL Server parse varchar string

Posted on 2016-08-25
5
Medium Priority
?
48 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
  • 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 27

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 27

Accepted Solution

by:
Zberteoc earned 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

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