Solved

SQL Server parse varchar string

Posted on 2016-08-25
5
27 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 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now