Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Substring and Charindex to split a text in to columns Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 4:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 28, 2014 1:42 AM
Points: 11, Visits: 103
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
Post #1397680
Posted Tuesday, December 18, 2012 4:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 953, Visits: 2,626
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
Post #1397684
Posted Tuesday, December 18, 2012 6:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 28, 2014 1:42 AM
Points: 11, Visits: 103
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
Post #1397754
Posted Tuesday, December 18, 2012 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1397805
Posted Wednesday, December 19, 2012 5:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 28, 2014 1:42 AM
Points: 11, Visits: 103
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
)

Post #1398333
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse