Extract data based on ||" symbol in SQL

Question

5.00/5 (1 vote)

See more:

I have table with a column called details,the values are separated by "|" symbol,i need to extract those data by "|" and insert into new table with fields,can anyone please help me to extract data?

VB

Name=Jhon|Age=36|Job=Sales Manager|Job Location=Texas|
Name=Tom|Age=27|Job=Sales Man|Job Location=Texas|
Name=Ferdinan|Age=38|Job=Sales Man|Job Location=Texas|
Name=Jhonson|Age=29|Job=Marketing Manager|Job Location=Texas|
Name=Mikel|Age=26|Job=Technician|Job Location=Texas|
Name=Steve|Age=25|Job=Technician|Job Location=Los Angeles|
Name=Rob|Age=29|Job=Programmer|Job Location=NC|

Posted 9-Jan-15 8:26am

Member 4650287

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-01-09T08:39:00

Solution 1

See here: Converting comma separated data in a column to rows for selection[^] - it assumes a commas as the separator but that's trivial to change.

Posted 9-Jan-15 8:39am

OriginalGriff

PIEBALDconsult · Answer 2 · 2015-01-09T09:33:00

If SQL Server... for that particular data -- you can make it into XML and query it:

SQL

WITH [raw] AS
(
  SELECT 'Name=Jhon|Age=36|Job=Sales Manager|Job Location=Texas|' Data
UNION ALL
  SELECT 'Name=Tom|Age=27|Job=Sales Man|Job Location=Texas|' Data
UNION ALL
  SELECT 'Name=Ferdinan|Age=38|Job=Sales Man|Job Location=Texas|' Data
UNION ALL
  SELECT 'Name=Jhonson|Age=29|Job=Marketing Manager|Job Location=Texas|' Data
UNION ALL
  SELECT 'Name=Mikel|Age=26|Job=Technician|Job Location=Texas|' Data
UNION ALL
  SELECT 'Name=Steve|Age=25|Job=Technician|Job Location=Los Angeles|' Data
UNION ALL
  SELECT 'Name=Rob|Age=29|Job=Programmer|Job Location=NC|' Data
)
, [cooked] AS
(
  SELECT CAST('<Data ' + REPLACE(REPLACE(REPLACE(Data,'Job ',''),'=','="'),'|','" ') + ' />' AS XML ) Data
  FROM [raw]
)
SELECT Data.value ( '(*/@Name)[1]' , 'VARCHAR(255)' ) Name
,  Data.value ( '(*/@Age)[1]' , 'int' ) Age
,  Data.value ( '(*/@Job)[1]' , 'VARCHAR(255)' ) Job
,  Data.value ( '(*/@Location)[1]' , 'VARCHAR(255)' ) Location
FROM [cooked]

(Note that I had to remove 'Job ' to produce well-formed XML.)

http://msdn.microsoft.com/en-us/library/ms178030.aspx[^]