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 12»»

Split function in sql Expand / Collapse
Author
Message
Posted Monday, November 11, 2013 12:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 10:23 AM
Points: 40, Visits: 259
Hi ,

I need the store procedure which i need to get the comma seperator values result in different columns and pipe line seperator in in next row
i am passing the data through parameter values are suppose (4,2,true,true,true,false|4,2,true,true,true,true)
pipeline seperator is in next row

1 4 2 TRUE TRUE FALSE FALSE
2 4 2 TRUE TRUE TRUE FALSE

then the result set i need to update in the database which base on primary key first column is id column which is my primary key

Can any one help me

Post #1513009
Posted Monday, November 11, 2013 1:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 4,908, Visits: 8,595
I think you'd better use table valued parameters rather than concatenated values.
An example is available here:
http://www.codeproject.com/Articles/37174/Table-Value-Parameter-in-SQL-Server-2008


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1513026
Posted Monday, November 11, 2013 1:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
create function dbo.SplitString 
(
@str nvarchar(max),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
cast(1 as bigint),
cast(1 as bigint),
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 ItemIndex,
substring(
@str,
a,
case when b > 0 then b-a ELSE LEN(@str) end)
AS Item
from tokens
);


USe this function


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1513030
Posted Monday, November 11, 2013 2:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 10:23 AM
Points: 40, Visits: 259
Hi , Thanks for your reply

i had tried this already if i pass the data in the paramater as ('1,4,2,2|2,4,3,5','|')

I am getting the result set as

ItemIndex Item
0 1,4,2,2
1 2,4,3,5



now i need the 'Item' column data into different columns
as
ID Item1 Item2 Item3
1 4 2 2
2 4 3 5

Post #1513042
Posted Monday, November 11, 2013 12:09 PM
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 @ 4:39 AM
Points: 818, Visits: 2,485
Jeff Moden has an excellent splitter here that performs extremely quickly. Once you have that, you can do the following
select rowseq,
min(col1) item1,
min(col2) item2,
min(col3) item3,
min(col4) item4,
min(col5) item5,
min(col6) item6
from (
select ps.itemnumber rowseq,
case when cs.ItemNumber = 1 then cs.Item else null end col1,
case when cs.ItemNumber = 2 then cs.Item else null end col2,
case when cs.ItemNumber = 3 then cs.Item else null end col3,
case when cs.ItemNumber = 4 then cs.Item else null end col4,
case when cs.ItemNumber = 5 then cs.Item else null end col5,
case when cs.ItemNumber = 6 then cs.Item else null end col6
from dbo.DelimitedSplit8K( '4,2,true,true,true,false|4,2,true,true,true,true' , '|') ps -- Split the string into rows on |
cross apply ( select * from dbo.DelimitedSplit8K( ps.item , ',') ) cs -- Split the rows on ,'s
) a
group by rowseq

The previous splitter function uses a counting recursive CTE. This can be a real performance killer.

If you are only doing four values like your second example string, the query could be reduced to the following
select parsename(replace(item,',','.'), 4) id,
parsename(replace(item,',','.'), 3) item1,
parsename(replace(item,',','.'), 2) item2,
parsename(replace(item,',','.'), 1) item3
from dbo.DelimitedSplit8K( '1,4,2,2|2,4,3,5' , '|');

Post #1513215
Posted Monday, November 11, 2013 2:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
Bhuvnesh (11/11/2013)
create function dbo.SplitString 
(
@str nvarchar(max),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
cast(1 as bigint),
cast(1 as bigint),
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 ItemIndex,
substring(
@str,
a,
case when b > 0 then b-a ELSE LEN(@str) end)
AS Item
from tokens
);


USe this function


Have you ever tested that for performance?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1513262
Posted Tuesday, November 12, 2013 3:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
Jeff Moden (11/11/2013)
Have you ever tested that for performance?
I tested it for small strings but not for big ones.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1513384
Posted Tuesday, November 12, 2013 3:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 10:23 AM
Points: 40, Visits: 259

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[USP_UpdateGroupAccess](@VALUE NVARCHAR(MAX),@STRING NVARCHAR(10))
AS

CREATE TABLE #GROUPACCESS(ID INT, DATA VARCHAR(100))
DECLARE @TEMP1 TABLE

(ID INT IDENTITY(1,1),
VALUE VARCHAR(MAX) )

INSERT INTO @TEMP1 SELECT * FROM DBO.SPLIT(@VALUE,@STRING)
--- DBO.SPLIT FUNCTION WILL SPLIT THE DATA INTO DIFFERENT ROW DEPENDS ON THE STRING WHICH WE WILL BE PASSING
----SELECT * FROM @TEMP1
INSERT INTO #GROUPACCESS SELECT * FROM @TEMP1

DECLARE @PIVOT VARCHAR(MAX)
DECLARE @SELECT VARCHAR(MAX)

SELECT
@PIVOT=COALESCE(@PIVOT+',','')+'[COL'+CAST(NUMBER+1 AS VARCHAR(10))+']'
FROM
MASTER..SPT_VALUES WHERE TYPE='P' AND
NUMBER<=(SELECT MAX(LEN(DATA)-LEN(REPLACE(DATA,',',''))) FROM #GROUPACCESS)
--- THIS SELECT STATEMENT WILL SPLIT THE DATA ROWS INTO COLUMNS USING THE PIVOT THE KEY FROM THE RESULT SET WHICH HAS BEEN STORED IN THE @TEMP1
SELECT
@SELECT='
SELECT P.*
FROM (
SELECT
ID,SUBSTRING(DATA, START+2, ENDPOS-START-2) AS TOKEN,
''COL''+CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY START) AS VARCHAR(10)) AS N
FROM (
SELECT
ID, DATA, N AS START, CHARINDEX('','',DATA,N+2) ENDPOS
FROM (SELECT NUMBER AS N FROM MASTER..SPT_VALUES WHERE TYPE=''P'') NUM
CROSS JOIN
(
SELECT
ID, '','' + DATA +'','' AS DATA
FROM
#GROUPACCESS
) M
WHERE N < LEN(DATA)-1
AND SUBSTRING(DATA,N+1,1) = '','') AS DATA
) PVT
PIVOT ( MAX(TOKEN)FOR N IN ('+@PIVOT+'))P'

-- EXEC(@SELECT) -- IF WE NEED TO CHECK THE RESULT FROM THE STORE PROCEDURE


CREATE TABLE #ADGROUPACCESS(ID INT NOT NULL,
[ADGROUPACCESSID] [SMALLINT] NOT NULL,
[ADGROUPID] [SMALLINT] NOT NULL,
[SUBFEATUREID] [SMALLINT] NOT NULL,
[ISVIEWALLOWED] [BIT] NULL,
[ISADDALLOWED] [BIT] NULL,
[ISDELETEALLOWED] [BIT] NULL,
[ISEDITALLOWED] [BIT] NULL,
[ISAUTHALLOWED] [BIT] NULL

)


INSERT INTO #ADGROUPACCESS EXEC(@SELECT)

SELECT * FROM #ADGROUPACCESS

UPDATE [ADGROUPACCESS] SET [ADGROUPID]= #ADGROUPACCESS.[ADGROUPID]
,[SUBFEATUREID]=#ADGROUPACCESS.[SUBFEATUREID]
,[ISVIEWALLOWED]=#ADGROUPACCESS.[ISVIEWALLOWED]
,[ISADDALLOWED]=#ADGROUPACCESS.[ISADDALLOWED]
,[ISDELETEALLOWED]=#ADGROUPACCESS.[ISDELETEALLOWED]
,[ISEDITALLOWED]=#ADGROUPACCESS.[ISEDITALLOWED]
,[ISAUTHALLOWED]=#ADGROUPACCESS.[ISAUTHALLOWED]
FROM [ADGROUPACCESS],#ADGROUPACCESS WHERE #ADGROUPACCESS.[ADGROUPACCESSID]=[ADGROUPACCESS].[ADGROUPACCESSID]


DROP TABLE #GROUPACCESS
DROP TABLE #ADGROUPACCESS

--EXECUTE USP_UPDATEGROUPACCESS '1,4,2,TRUE,TRUE,False,False,TRUE|2,4,2,TRUE,TRUE,False,TRUE,False','|'



try this
Post #1513388
Posted Tuesday, November 12, 2013 8:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
Bhuvnesh (11/12/2013)
Jeff Moden (11/11/2013)
Have you ever tested that for performance?
I tested it for small strings but not for big ones.


I kind'a figured that. The code you're using is known as an "inch worm" splitter and, although I've not tested your specific code yet, they tend to be comparatively slow in the 8K range. I'll try to find the time to gin up a test to demonstrate what I'm talking about. In the mean time, please have a look at the following articles. The first explains a different method and the second explains why you might not want to use an rCTE for such a thing.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
http://www.sqlservercentral.com/articles/T-SQL/74118/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1513469
Posted Tuesday, November 12, 2013 11:36 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 @ 4:39 AM
Points: 818, Visits: 2,485
Hi nhimabindhu

Looking at the procedure that you posted, I think this is what you are trying to do.
Note that I am using the DelimitedSplit8k function previously mentioned.

Create PROCEDURE [dbo].[USP_UpdateGroupAccess](
@VALUE VARCHAR(8000), -- HOW BIG IS THIS LIKELY TO GET?
@STRING VARCHAR(1)
)
AS
BEGIN
WITH updateSet AS (
SELECT rowseq,
adgroupaccessid = min(col1) ,
adgroupid = min(col2) ,
subfeatureid = min(col3) ,
isviewallowed = min(col4) ,
isaddallowed = min(col5) ,
isdeleteallowed = min(col6) ,
iseditallowed = min(col7) ,
isauthallowed = min(col8)
FROM (
SELECT ps.itemnumber rowseq,
case when cs.ItemNumber = 1 then cs.Item else null end col1,
case when cs.ItemNumber = 2 then cs.Item else null end col2,
case when cs.ItemNumber = 3 then cs.Item else null end col3,
case when cs.ItemNumber = 4 then cs.Item else null end col4,
case when cs.ItemNumber = 5 then cs.Item else null end col5,
case when cs.ItemNumber = 6 then cs.Item else null end col6,
case when cs.ItemNumber = 7 then cs.Item else null end col7,
case when cs.ItemNumber = 8 then cs.Item else null end col8
FROM dbo.DelimitedSplit8K( @VALUE , @STRING) ps -- Split the string into rows on |
CROSS APPLY ( SELECT * FROM dbo.DelimitedSplit8K( ps.item , ',') ) cs -- Split the rows on ,'s
) a
GROUP BY rowseq
)
SELECT -- UNCOMMENT THIS AND COMMENT NEXT TO TEST
-- UPDATE A SET --UNCOMMENT THIS AND COMMENT PREV TO UPDATE
adgroupaccessid = u.adgroupaccessid ,
adgroupid = u.adgroupid ,
subfeatureid = u.subfeatureid ,
isviewallowed = u.isviewallowed ,
isaddallowed = u.isaddallowed ,
isdeleteallowed = u.isdeleteallowed ,
iseditallowed = u.iseditallowed ,
isauthallowed = u.isauthallowed
FROM updateSet u
INNER JOIN [ADGROUPACCESS] A ON a.[ADGROUPACCESSID]=u.[ADGROUPACCESSID];
END

Post #1513573
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse