Parse the field

I have a table that has a "Problem" field with the following data
Customer Service...Jam...Jam 5...Planned
Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
New Drive
Sale Made
I need to separate this "Problem" field into separate fields the issue1,
issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
) .
What would be the easiest way to do so within the SELECT statement ?here's a quick and dirty way of parsing out the data:::
declare @.myString varchar(2000) --Set to max length of field
declare @.Issue1 varchar(100) --Set to max length of any single entry
set @.myString = 'Customer Service...Jam...Jam 5...Planned '
set @.myString = 'Technical
Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
--set @.myString = 'New Drive'
declare @.Index int -- Holds the next position of delimeter
declare @.delimiter char(1) -- holds delimeter character
set @.delimiter = '.' -- set the delimer value
set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
of delimeter
-- run a loop while we still have delimeters in the string
while @.Index>0
begin
-- get the next value
set @.Issue1 = substring(@.myString,1,@.Index-1)
-- do whatever you wish with the value print, save to table, array, etc)
print @.Issue1
-- parse the latest value from the current string
set @.myString = substring(@.myString,@.Index+3,len(@.myString))
-- get the next instance of the delimeter
set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
end
-- Get the last value from what's left
set @.Issue1 = ltrim(rtrim(@.myString))
-- do whatever you wish with the value print, save to table, array, etc)
print @.Issue1
Copy and paste it into QA and you can see how it works for you
Tony
"agenda9533" wrote:
> I have a table that has a "Problem" field with the following data
> Customer Service...Jam...Jam 5...Planned
> Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> New Drive
> Sale Made
> I need to separate this "Problem" field into separate fields the issue1,
> issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> ) .
> What would be the easiest way to do so within the SELECT statement ?|||This is awesome!
Only thing, Ijust don't know how to run/call this script within RS.
As far as I know RS only recognize T-Sql SELECT statements (in order to
create a dataset).
Am I wrong?
"Logicalman" wrote:
> here's a quick and dirty way of parsing out the data:::
>
> declare @.myString varchar(2000) --Set to max length of field
> declare @.Issue1 varchar(100) --Set to max length of any single entry
> set @.myString = 'Customer Service...Jam...Jam 5...Planned '
> set @.myString = 'Technical
> Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
> --set @.myString = 'New Drive'
> declare @.Index int -- Holds the next position of delimeter
> declare @.delimiter char(1) -- holds delimeter character
> set @.delimiter = '.' -- set the delimer value
> set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
> of delimeter
> -- run a loop while we still have delimeters in the string
> while @.Index>0
> begin
> -- get the next value
> set @.Issue1 = substring(@.myString,1,@.Index-1)
> -- do whatever you wish with the value print, save to table, array, etc)
> print @.Issue1
> -- parse the latest value from the current string
> set @.myString = substring(@.myString,@.Index+3,len(@.myString))
> -- get the next instance of the delimeter
> set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
> end
> -- Get the last value from what's left
> set @.Issue1 = ltrim(rtrim(@.myString))
> -- do whatever you wish with the value print, save to table, array, etc)
> print @.Issue1
>
> Copy and paste it into QA and you can see how it works for you
> Tony
> "agenda9533" wrote:
> > I have a table that has a "Problem" field with the following data
> >
> > Customer Service...Jam...Jam 5...Planned
> >
> > Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> > New Drive
> >
> > Sale Made
> >
> > I need to separate this "Problem" field into separate fields the issue1,
> > issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> > ) .
> >
> > What would be the easiest way to do so within the SELECT statement ?|||My first suggestion would be to place this in a Stored Procedure, and call
the SProc as the dataset for the RS report.
"agenda9533" wrote:
> This is awesome!
> Only thing, Ijust don't know how to run/call this script within RS.
> As far as I know RS only recognize T-Sql SELECT statements (in order to
> create a dataset).
> Am I wrong?
> "Logicalman" wrote:
> > here's a quick and dirty way of parsing out the data:::
> >
> >
> > declare @.myString varchar(2000) --Set to max length of field
> > declare @.Issue1 varchar(100) --Set to max length of any single entry
> >
> > set @.myString = 'Customer Service...Jam...Jam 5...Planned '
> > set @.myString = 'Technical
> > Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
> > --set @.myString = 'New Drive'
> >
> > declare @.Index int -- Holds the next position of delimeter
> > declare @.delimiter char(1) -- holds delimeter character
> > set @.delimiter = '.' -- set the delimer value
> >
> > set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
> > of delimeter
> > -- run a loop while we still have delimeters in the string
> > while @.Index>0
> > begin
> > -- get the next value
> > set @.Issue1 = substring(@.myString,1,@.Index-1)
> > -- do whatever you wish with the value print, save to table, array, etc)
> > print @.Issue1
> > -- parse the latest value from the current string
> > set @.myString = substring(@.myString,@.Index+3,len(@.myString))
> > -- get the next instance of the delimeter
> > set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
> >
> > end
> > -- Get the last value from what's left
> > set @.Issue1 = ltrim(rtrim(@.myString))
> > -- do whatever you wish with the value print, save to table, array, etc)
> > print @.Issue1
> >
> >
> > Copy and paste it into QA and you can see how it works for you
> >
> > Tony
> >
> > "agenda9533" wrote:
> >
> > > I have a table that has a "Problem" field with the following data
> > >
> > > Customer Service...Jam...Jam 5...Planned
> > >
> > > Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> > > New Drive
> > >
> > > Sale Made
> > >
> > > I need to separate this "Problem" field into separate fields the issue1,
> > > issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> > > ) .
> > >
> > > What would be the easiest way to do so within the SELECT statement ?