Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Services / Reporting Services / June 2005

Tip: Looking for answers? Try searching our database.

Parse the field

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
agenda9533 - 30 Jun 2005 16:22 GMT
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 ?
Logicalman - 30 Jun 2005 17:08 GMT
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

> I have a table that has a "Problem" field with the following data
>
[quoted text clipped - 10 lines]
>
> What would be the easiest way to do so within the SELECT statement ?
agenda9533 - 30 Jun 2005 17:19 GMT
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?

> here's a quick and dirty way of parsing out the data:::
>
[quoted text clipped - 49 lines]
> >
> > What would be the easiest way to do so within the SELECT statement ?
Logicalman - 30 Jun 2005 17:22 GMT
My first suggestion would be to place this in a Stored Procedure, and call
the SProc as the dataset for the RS report.

> This is awesome!
> Only thing, Ijust don't know how to run/call this script within RS.
[quoted text clipped - 55 lines]
> > >
> > > What would be the easiest way to do so within the SELECT statement ?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.