December 18, 2012 at 4:16 am
Hello everyone,
I am struggeling with below:
I have a field in ReportServer DB called “C.Path AS OrgInput (VARCHAR)”. In this field the data has been saved like:
-/Adhoc/CRM/Complaints report/Report on owner level
-/Adhoc/Finance/General Ledger/Spend Analysis report
I am trying to achieve by using substring and charindex to split the data of this field in to columns separated by slash "/". So the result of "OrgInput" would be something like this:
Column1 | Column2 | Column3 | Column4
--------------------------------------------------------------------
/Adhoc | /CRM | /Complaints report | /Report on owner level
/Adhoc | /Finance | /General Ledger | /Spend Analysis report
Is there anyone who can help me or give me some advice of where to start.
This is my query so far:
SELECT C.Path AS OrgInput
, CHARINDEX('/', C.Path, 2) AS [FirstPos]
, SUBSTRING(C.Path -- String
, CHARINDEX('/', C.Path, 2)+1 -- StartPos
, LEN(C.Path) -- Length
) AS StartText
, LEFT(SUBSTRING(C.Path -- String
, CHARINDEX('/', C.Path, 2)+1 -- StartPos
, LEN(C.Path) -- Length
)
, 8
) AS FirstResult
, LEFT(SUBSTRING(C.Path -- String
, CHARINDEX('/', C.Path, 2)+1 -- StartPos
, LEN(C.Path) -- Length
)
, CHARINDEX('/', C.Path, (CHARINDEX('/', C.Path, 2)+1)) -- Test toegevoegd
) AS SecondResult
, CHARINDEX('/', C.Path, (CHARINDEX('/', C.Path, 2)+1)) AS [Second/]
, SUBSTRING(C.Path
, CHARINDEX('/', C.Path, (CHARINDEX('/', C.Path, 2)+1))+1
, LEN(C.Path)
)
FROM dbo.Catalog C WITH(NOLOCK) -- 773 Report Catalog
WHERE 1=1
AND C.Type = 2
ORDER BY 1
/*
SUBSTRING(Text, Start, Length)
LEFT(Text, Length) --> Keep 3 most left chars
RIGHT(Text, Length) --> Keep 3 most right chars
LEN(Text) --> Length
*/
BR,
Eidjaz
December 18, 2012 at 4:20 am
Hi,
I would start by reading this article first, http://www.sqlservercentral.com/articles/Tally+Table/72993/
Its got a great String splitter function.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 18, 2012 at 6:27 am
Jason-299789 (12/18/2012)
Hi,I would start by reading this article first, http://www.sqlservercentral.com/articles/Tally+Table/72993/
Its got a great String splitter function.
Thanks i will read the article and see if i can get wiser ;).
Eidjaz
December 18, 2012 at 7:48 am
Here is an example using the DelimitedSplit8K function that you can find at the link provided, or you can follow the link in my signature for splitting strings.
;with cte (Col1) as
(
select '/Adhoc/CRM/Complaints report/Report on owner level' union all
select '/Adhoc/Finance/General Ledger/Spend Analysis report'
)
select *
from cte
cross apply dbo.DelimitedSplit8K(right(Col1, len(Col1) -1), '/')
Notice how I posted data in an easily consumable format. You should consider that on future posts.
Also, I noticed you are using the NOLOCK hint. Are you aware of the challenges that hint brings to the table? It will help your code run faster but it comes at a cost. Most people say "I don't care about dirty reads". This hint has far greater implications than just dirty reads. You can missing or even duplicate information. Here are a couple of articles that explains this hint in greater detail.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 19, 2012 at 5:33 am
Sean Lange (12/18/2012)
Here is an example using the DelimitedSplit8K function that you can find at the link provided, or you can follow the link in my signature for splitting strings.
;with cte (Col1) as
(
select '/Adhoc/CRM/Complaints report/Report on owner level' union all
select '/Adhoc/Finance/General Ledger/Spend Analysis report'
)
select *
from cte
cross apply dbo.DelimitedSplit8K(right(Col1, len(Col1) -1), '/')
Notice how I posted data in an easily consumable format. You should consider that on future posts.
Also, I noticed you are using the NOLOCK hint. Are you aware of the challenges that hint brings to the table? It will help your code run faster but it comes at a cost. Most people say "I don't care about dirty reads". This hint has far greater implications than just dirty reads. You can missing or even duplicate information. Here are a couple of articles that explains this hint in greater detail.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Thanks a alot for the example. It really did help me, this is how my qeury look like:
SELECT(SELECT C.Path AS Path, C.Name AS ReportName
, CASE WHEN C.Hidden = 1 THEN 'True'
ELSE 'False' END AS ReportHide
, COALESCE(C.Description, '<none>') AS ReportDesc
, UC.UserName AS ReportCreatedBy, C.CreationDate AS ObjectCreatedDate
, UM.UserName AS ReportModifiedBy, C.ModifiedDate AS ObjectModifedDate
, D.*
FROM dbo.Catalog C
INNER JOIN dbo.Users UC WITH(NOLOCK)
ON C.CreatedByID = UC.UserID
INNER JOIN dbo.Users UM WITH(NOLOCK)
ON C.ModifiedByID = UM.UserID
CROSS APPLY Ceyenne_JDE.dbo.DelimitedSplit8K(C.Path, '/') D
WHERE 1=1
AND C.Type = 2
--AND C.Path = '/Adhoc/CRM/20121024 CRM complaints report on owner level'
) Q
PIVOT ( MAX(Item)
FOR ItemNumber IN ([2], [3], [4], [5], [6], [7], [8])
) AS pvt
)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply