Parse sting with quotes in SQL

If, I have a string as follows , how Do I parse these quotes, such that it gives me 3 fields.

Eg of string is

Status changes "PASS" "FAIL" By "PW" '"/3/12/2007"

Resuls set should look like:

Column 1 Column 2 Column 3

PASS FAIL 3/12/2007

Pl advise.

Use a combination of the replace() function and Jen Suessmeyer's split function.

If there are no 'spaces' in the data, a single pass replacing the double quote with an empty string would work.

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||Do you really need them in separate columns? If rows will do, then you could use Itzik's split function. Check insidetsql.com for the script.

Rob|||

this is a code to break a string "first"Second"third"Forth"Fifth"Sixth"" into First Second third forth etc however u have to pass and Extra " in the end of the string

declare @.Group varchar(8000)
Create table #Temp(GroupName varchar(100))
Declare @.Cnt int,@.CharLocation int,@.Previous int,@.Extent int
Set @.Group='"First"Second"Third"Forth"Fifth"Sixth""'
Set @.Cnt=1
Set @.Previous=1
Set @.Extent=1
While @.Cnt<>Len(@.Group)
BEgin
if substring(@.Group,@.Cnt,1)='"'
BEgin
--Select Substring(@.GroupName,@.Previous,@.Extent-1)
insert into #Temp values(Substring(@.Group,@.Previous,@.Extent-1))

sET @.Previous=@.Cnt+1
Set @.Extent=0
END
Set @.Extent=@.Extent+1
Set @.Cnt=@.Cnt+1
END
select * from #temp where GroupName <>''
drop table #Temp

try doing some manipulations and see if this code is of any help

RegarDs,

Jacx

|||I need this to be split into columns, the code above splits it into rows|||Jen's Split function I offered does split into columns -but of course you didn't try it, did you?|||

I did try Jens split function. I created that in the dbo, BUT it created that function as a type='TF', Not 'FN'.

So when I actually call the function, it gives err message invalid object.

This is how I call the Split Function .

select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

and it errs out with the message below, though the split object does exist in the db. Any suggestions.

Thanks much.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Split'.

|||

You made a simple error. The function is a table valued function (TVF or "TF") That means that you use it like a table.

Instead of:

select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Try:

SELECT * FROM dbo.Split( '"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Also, recall my earlier comment about needing to use the replace() function to remove the quotes and/or commas.|||

I have 2 issues:

1. I need the string split into multiple columns, not just 1. ie COL1 Shoud have "mod PEN" ,

Col2 should hv "approv" Col3 should hv "by wagety" and Col4 "3/21/2007"'. This split function returns everthying in 1 column.

2. Also, I have these values that are to be split, stored in a table. eg there is a table called notes, with a field called status.

That field status will have that records, which will have values such as "mod PEN" "approved" "by wagety" "3/21/2007"' this entire string will be 1 row. How can I use this function, for that tbl notes?

|||

1. I thought I understood that was your request.

Jen's split function will separte into different columns, if you are having difficulty, you may not have identified and be handling the delimiters correctly.

2. You have completely confused me now.

I previously understood that you wanted to separate

"PASS" "FAIL" By "PW" '"/3/12/2007"

into separate parts -at least that is what your example seemed to indicate that you wanted.

Now, it seems that you want to store all of the parts in one field called 'Status'.

Why not just store the current string in the Status field and be done?

|||

1. This is how I call Jens split function and it return all the values In 1 Single Column: eg

select * from dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

When you execute this, it returns the following , into 1 Column called SplitValue . I need the values between the doubel quotes, split into different columns, ie in this case 4 diff cols, ie Col1 should have mod PEN, Col2 should have approved, Colum # 3 shd have By wagety, Col 4 should have 3/21/2007. Can you pl assist

Output returns the foll:

OccurenceId SplitValue
-- --
1 "mod PEN" "approved" "by wagety" "3/21/2007"