SPLIT AN ARRAY!

PedroCGD

dear friends,

I have a stored procedure that receives a parameter like ' 123#67#1#89#52#'

I need to insert each number (123, 67, 1...) in a table... How can I do it using the best performance

Thanks!!!

Cheers!!!

Dinakar


You could write a function that takes the string and returns a table (table-valued function in BOL).

PedroCGD

How can I do it !

Could you give me an example

thanks

Jens K. Suessmeyer

Hi,

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=326300&SiteID=1

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

PedroCGD

Jean,

But I need is to extract each field in a while, and insert each extracted value in one table.

The example you gave me is not too long for my case

Thanks!!

Phil Brammer

PedroCGD wrote:

Jean, But I need is to extract each field in a while, and insert each extracted value in one table. The example you gave me is not too long for my case Thanks!!

I believe that what Jens posted will work just fine. Did you read the bottom part of that where he actually issues the SQL statement that calls the function In there he passes in the string to be split and it goes from there. The key is in that last SQL statement and how you make it work for your situation. The function created will work though.

PedroCGD

I made like this an have a error:

ALTER procedure [dbo].[ST_SP_INS_PROJECTO]

(

@P_Nome nvarchar(50),

@P_Data nvarchar(10),

@P_CreatedBy nvarchar(20),

@P_Descricao nvarchar(200),

@P_Owner nvarchar(50),

@P_Autorizacoes nvarchar(255),

@P_KeyUser nvarchar(150),

@P_Users nvarchar(255),

@P_Manutencao nvarchar(255),

@P_Storage decimal(18,2),

@P_Observacoes nvarchar(250),

@P_pas_du nvarchar(50),

@P_pas_fs nvarchar(50),

@P_pfs_du nvarchar(50),

@P_pfs_fs nvarchar(50),

@P_Adminstracao nvarchar(50),

@P_Piquete nvarchar(50),

@P_arrServidores nvarchar(200)

)

AS

DECLARE @IdentityNo int

set nocount on

INSERT INTO PROJECTO

(P_Nome,P_Data, P_CreatedBy, P_Descricao, P_Owner, P_Autorizacoes, P_KeyUser, P_Users, P_Manutencao, P_Storage, P_Observacoes, P_pas_du, P_pas_fs, P_pfs_du, P_pfs_fs, P_Adminstracao, P_Piquete)

VALUES

(@P_Nome,@P_Data, @P_CreatedBy, @P_Descricao, @P_Owner, @P_Autorizacoes, @P_KeyUser, @P_Users, @P_Manutencao, @P_Storage, @P_Observacoes, @P_pas_du, @P_pas_fs, @P_pfs_du, @P_pfs_fs, @P_Adminstracao, @P_Piquete)

RETURN @@identity

-- MY SPLIT SUBJECT :

DECLARE @SplitLength INT

WHILE LEN(@P_arrServidores) > 0

BEGIN

SELECT @SplitLength = (CASE CHARINDEX('#',@P_arrServidores) WHEN 0

THEN LEN(@P_arrServidores)

ELSE CHARINDEX('#',@P_arrServidores) -1 END)

INSERT INTO dbo.PROJECTO_SERVIDOR (PS_P_ID, PS_S_ID)

VALUES (@IdentityNo, SELECT SUBSTRING(@P_arrServidores,1,@SplitLength) )

SELECT @P_arrServidores = (CASE (LEN(@P_arrServidores) - @SplitLength) WHEN 0 THEN ''

ELSE RIGHT(@P_arrServidores, LEN(@P_arrServidores) - @SplitLength - 1) END)

END

ERROR:

Msg 156, Level 15, State 1, Procedure ST_SP_INS_PROJECTO, Line 48

Incorrect syntax near the keyword 'SELECT'.

Msg 102, Level 15, State 1, Procedure ST_SP_INS_PROJECTO, Line 48

Incorrect syntax near ')'.

Phil Brammer

PedroCGD wrote:

I made like this an have a error: .... BEGIN SELECT @SplitLength = (CASE CHARINDEX('#',@P_arrServidores) WHEN 0 THEN LEN(@P_arrServidores) ELSE CHARINDEX('#',@P_arrServidores) -1 END) INSERT INTO dbo.PROJECTO_SERVIDOR (PS_P_ID, PS_S_ID) VALUES (@IdentityNo, SELECT SUBSTRING(@P_arrServidores,1,@SplitLength) ) SELECT @P_arrServidores = (CASE (LEN(@P_arrServidores) - @SplitLength) WHEN 0 THEN '' ELSE RIGHT(@P_arrServidores, LEN(@P_arrServidores) - @SplitLength - 1) END) END ERROR: Msg 156, Level 15, State 1, Procedure ST_SP_INS_PROJECTO, Line 48 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Procedure ST_SP_INS_PROJECTO, Line 48 Incorrect syntax near ')'.

You can fix that error by getting rid of your parenthesis around the case statements. Plus, they don't even match.

Mark - SQL

Using a numbers table as in

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

you can do this

declare @s varchar(100)

set @s='123#67#1#89#52#'

select cast(substring(@s,
Number,
charindex('#',
@s + '#',
Number) - Number) as int)
from Numbers
where Number between 1 and len(@s) + 1
and substring('#' + @s, Number, 1) = '#'
and charindex('#',@s + '#',Number)>Number

PedroCGD

Phil,

I cut the parentesis and the error still there! :-(

Thanks!

ManiD

Remove the Select Key word after the @IdentityNo on the Insert Statement..

INSERT INTO dbo.PROJECTO_SERVIDOR (PS_P_ID, PS_S_ID)

VALUES (@IdentityNo, Select SUBSTRING(@P_arrServidores,1,@SplitLength) )

--It Should be

INSERT INTO dbo.PROJECTO_SERVIDOR (PS_P_ID, PS_S_ID)

VALUES (@IdentityNo, SUBSTRING(@P_arrServidores,1,@SplitLength) )

PedroCGD

Dear Mark,

Your example works, but I need to create a new table. Probably is more fast and data is more consistent, but is one more object... I will think on it... :-) Unless I have one solution for my problem! :-)

Thanks!

PedroCGD

Using the answer of Mark, I will try the answer of Phil....

This Stored procedure only do the insert and delete statement if I cut @@identity. And the FETCH doesnt work.... I want to insert the values of string in one table... Could help me Thanks...

ALTER procedure [dbo].[ST_SP_INS_PROJECTO]

(

@P_Nome nvarchar(50),

@P_Data nvarchar(10),

@P_CreatedBy nvarchar(20),

@P_Descricao nvarchar(200),

@P_Owner nvarchar(50),

@P_Autorizacoes nvarchar(255),

@P_KeyUser nvarchar(150),

@P_Users nvarchar(255),

@P_Manutencao nvarchar(255),

@P_Storage decimal(18,2),

@P_Observacoes nvarchar(250),

@P_pas_du nvarchar(50),

@P_pas_fs nvarchar(50),

@P_pfs_du nvarchar(50),

@P_pfs_fs nvarchar(50),

@P_Adminstracao nvarchar(50),

@P_Piquete nvarchar(50),

@P_arrServidores nvarchar(200)

)

AS

DECLARE @IdentityNo int

DECLARE @cServidorID int

-- INITIALIZE VARIABLE WITH TEST VALUES

DECLARE @s varchar(100)

SET @s='123#67#1#89#52#'

-- MAIN QUERY

INSERT INTO PROJECTO

(P_Nome,P_Data, P_CreatedBy, P_Descricao, P_Owner, P_Autorizacoes, P_KeyUser, P_Users, P_Manutencao, P_Storage, P_Observacoes, P_pas_du, P_pas_fs, P_pfs_du, P_pfs_fs, P_Adminstracao, P_Piquete)

VALUES

(@P_Nome,@P_Data, @P_CreatedBy, @P_Descricao, @P_Owner, @P_Autorizacoes, @P_KeyUser, @P_Users, @P_Manutencao, @P_Storage, @P_Observacoes, @P_pas_du, @P_pas_fs, @P_pfs_du, @P_pfs_fs, @P_Adminstracao, @P_Piquete)

RETURN @@identity

-- INSERT EACH VALUE IN PROJECTO_SERVIDOR TABLE

DECLARE LINHAS_CURSOR CURSOR FOR SELECT Number FROM dbo.Numbers

WHERE CHARINDEX

(

'#'+CONVERT(VARCHAR(12),Number)+'#',

'#'+ @s +'#'

) > 0 ORDER BY Number

OPEN LINHAS_CURSOR

FETCH NEXT FROM LINHAS_CURSOR INTO @cServidorID

WHILE @@FETCH_STATUS=0

BEGIN

INSERT INTO PROJECTO_SERVIDOR (PS_P_ID, PS_S_ID)

VALUES (@@identity, @cServidorID)

FETCH NEXT FROM LINHAS_CURSOR INTO @cServidorID

END

CLOSE LINHAS_CURSOR

DEALLOCATE LINHAS_CURSOR

-- DELETE SOME DATA IN OTHER TABLE

EXECUTE GD_SP_DEL_ReparticaoByUserID @P_CreatedBy

PedroCGD

Manid and Phil,

It works, but I have a problem in the identity value... If I use @@identity of the first INSERT query to insert in the second, only insert one value... but I I use a number in spite of using @@identity, it works. How can insert the identity of first Insert as a value in the sencond insert

INSERT INTO dbo.PROJECTO_SERVIDOR (PS_P_ID, PS_S_ID)

VALUES (@@identity, SUBSTRING(@P_arrServidores,1,@SplitLength) )

I Thinks this answer is better than the other, because I dont need to create a table, and I dont need a fetch function to insert each value...

Could someone help me with the identity

Thanks

ALTER procedure [dbo].[ST_SP_INS_PROJECTO]

(

@P_Nome nvarchar(50),

@P_Data nvarchar(10),

@P_CreatedBy nvarchar(20),

@P_Descricao nvarchar(200),

@P_Owner nvarchar(50),

@P_Autorizacoes nvarchar(255),

@P_KeyUser nvarchar(150),

@P_Users nvarchar(255),

@P_Manutencao nvarchar(255),

@P_Storage decimal(18,2),

@P_Observacoes nvarchar(250),

@P_pas_du nvarchar(50),

@P_pas_fs nvarchar(50),

@P_pfs_du nvarchar(50),

@P_pfs_fs nvarchar(50),

@P_Adminstracao nvarchar(50),

@P_Piquete nvarchar(50),

@P_arrServidores nvarchar(200)

)

AS

DECLARE @IdentityNo int

set nocount on

INSERT INTO PROJECTO

(P_Nome,P_Data, P_CreatedBy, P_Descricao, P_Owner, P_Autorizacoes, P_KeyUser, P_Users, P_Manutencao, P_Storage, P_Observacoes, P_pas_du, P_pas_fs, P_pfs_du, P_pfs_fs, P_Adminstracao, P_Piquete)

VALUES

(@P_Nome,@P_Data, @P_CreatedBy, @P_Descricao, @P_Owner, @P_Autorizacoes, @P_KeyUser, @P_Users, @P_Manutencao, @P_Storage, @P_Observacoes, @P_pas_du, @P_pas_fs, @P_pfs_du, @P_pfs_fs, @P_Adminstracao, @P_Piquete)

-- MY SPLIT SUBJECT :

DECLARE @SplitLength INT

SET @P_arrServidores='123#67#1#89#52#'

WHILE LEN(@P_arrServidores) > 0

BEGIN

SELECT @SplitLength = CASE CHARINDEX('#',@P_arrServidores) WHEN 0

THEN LEN(@P_arrServidores)

ELSE CHARINDEX('#',@P_arrServidores) -1 END

INSERT INTO dbo.PROJECTO_SERVIDOR (PS_P_ID, PS_S_ID)

VALUES (@@identity, SUBSTRING(@P_arrServidores,1,@SplitLength) )

SELECT @P_arrServidores = CASE (LEN(@P_arrServidores) - @SplitLength) WHEN 0 THEN ''

ELSE RIGHT(@P_arrServidores, LEN(@P_arrServidores) - @SplitLength - 1) END

END

EXECUTE GD_SP_DEL_ReparticaoByUserID @P_CreatedBy

PedroCGD

Dear Friends, Thanks for all!!

IT WORKS!! I selected the answer of PHIL!

THANKS FOR ALL!!!

ALTER procedure [dbo].[ST_SP_INS_PROJECTO]

(

@P_Nome nvarchar(50),

@P_Data nvarchar(10),

@P_CreatedBy nvarchar(20),

@P_Descricao nvarchar(200),

@P_Owner nvarchar(50),

@P_Autorizacoes nvarchar(255),

@P_KeyUser nvarchar(150),

@P_Users nvarchar(255),

@P_Manutencao nvarchar(255),

@P_Storage decimal(18,2),

@P_Observacoes nvarchar(250),

@P_pas_du nvarchar(50),

@P_pas_fs nvarchar(50),

@P_pfs_du nvarchar(50),

@P_pfs_fs nvarchar(50),

@P_Adminstracao nvarchar(50),

@P_Piquete nvarchar(50),

@P_arrServidores nvarchar(200)

)

AS

DECLARE @IdentityNo int

-- MAIN QUERY

SET NOCOUNT ON

INSERT INTO PROJECTO

(P_Nome,P_Data, P_CreatedBy, P_Descricao, P_Owner, P_Autorizacoes, P_KeyUser, P_Users, P_Manutencao, P_Storage, P_Observacoes, P_pas_du, P_pas_fs, P_pfs_du, P_pfs_fs, P_Adminstracao, P_Piquete)

VALUES

(@P_Nome,@P_Data, @P_CreatedBy, @P_Descricao, @P_Owner, @P_Autorizacoes, @P_KeyUser, @P_Users, @P_Manutencao, @P_Storage, @P_Observacoes, @P_pas_du, @P_pas_fs, @P_pfs_du, @P_pfs_fs, @P_Adminstracao, @P_Piquete)

SET @IdentityNo= SCOPE_IDENTITY()

-- INSERT EACH VALUE IN PROJECTO_SERVIDOR TABLE

DECLARE @SplitLength INT

WHILE LEN(@P_arrServidores) > 0

BEGIN

SELECT @SplitLength = CASE CHARINDEX('#',@P_arrServidores) WHEN 0

THEN LEN(@P_arrServidores)

ELSE CHARINDEX('#',@P_arrServidores) -1 END

INSERT INTO dbo.PROJECTO_SERVIDOR (PS_P_ID, PS_S_ID)

VALUES (@IdentityNo, SUBSTRING(@P_arrServidores,1,@SplitLength) )

SELECT @P_arrServidores = CASE (LEN(@P_arrServidores) - @SplitLength) WHEN 0 THEN ''

ELSE RIGHT(@P_arrServidores, LEN(@P_arrServidores) - @SplitLength - 1) END

END

-- DELETE SOME DATA IN OTHER TABLE

EXECUTE GD_SP_DEL_ReparticaoByUserID @P_CreatedBy