Stored Proc to parse delimited string to table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Proc to parse delimited string to table

Author

Topic

MrMister17
Starting Member

9 Posts

Posted - 2015-04-10 : 14:28:29

Hello All,

Could some one please help me with a SP to parse a delimited string and insert the result in a table. I am using SQL Server 2008 R2. I have 2 tables - RawData & WIP. I have Robots on a manufacturing line capable of moving data to a DB. I move the raw data to RawData. On insert [RawData], I want to parse the string and move the contents to WIP as indicated below. I will run reports against the WIP Table.

Also, after the string is parsed, I'd like to change the Archive column, the _0 at the end of the raw string to 1 in the WIP table to indicate a successful parse.

Sample Strings - [RawData Table]
04102015_114830_10_013_9_8_6_99999_Test 1_1_0
04102015_115030_10_013_9_8_6_99999_Test 2_1_0

Desired Output - [WIP Table]

Date Time Plant Program Line Zone Station BadgeID Message Alarm Archive
-----------------------------------------------------------------------------------
04102015 114830 10 13 9 8 6 99999 Test 1 1 1
04102015 115030 10 13 9 8 6 99999 Test 2 1 1

Any help is appreciated. I am new to SQL but can dabble around.

Mohit.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 14:39:06

This is adapted from the following code : http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx

CREATE FUNCTION dbo.Split ( @strString varchar(4000),@Delimiter char(1))
RETURNS @Result TABLE(Value varchar(4000))
AS
BEGIN

DECLARE @x XML
SELECT @x = CAST('<A>'+ REPLACE(@strString,@Delimiter,'</A><A>')+ '</A>' AS XML)

INSERT INTO @Result
SELECT t.value('.', 'varchar(4000)') AS inVal
FROM @x.nodes('/A') AS x(t)

RETURN
END
GO

-- INSERT INTO SomeTable
SELECT * FROM dbo.Split ('04102015_114830_10_013_9_8_6_99999_Test 1_1_0','_')

MrMister17
Starting Member

9 Posts

Posted - 2015-04-10 : 14:53:04

Thanks Michael. I have seen this before, it doesn't create a row, but columnar answers. Also, I'd like a SP to fire on insert.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 15:04:38

apologies - I read too quickly

is this more of what you are trying to do less the procedure part? I gather your sting will always be the same number of delimited values - correct ?

ALTER FUNCTION dbo.Split ( @strString varchar(4000),@Delimiter char(1))
RETURNS @Result TABLE(ID int, Value varchar(4000))
AS
BEGIN

DECLARE @x XML
SELECT @x = CAST('<A>'+ REPLACE(@strString,@Delimiter,'</A><A>')+ '</A>' AS XML)

INSERT INTO @Result
SELECT ROW_NUMBER() OVER (ORDER BY @strString ) ID,t.value('.', 'varchar(4000)') AS inVal
FROM @x.nodes('/A') AS x(t)

RETURN
END
GO

-- INSERT INTO SomeTable
SELECT [1], [2], [3], [4], [5],[6], [7],

, [9], [10] , [11]
FROM
(SELECT *, 1 A FROM dbo.Split ('04102015_114830_10_013_9_8_6_99999_Test 1_1_0','_')
) p
PIVOT
(
MAX (VALUE)
FOR ID IN
( [1], [2], [3], [4], [5],[6], [7],

, [9], [10] , [11] )
) AS pvt

be aware - it looks like an eight ball replace the brackets with the number 8 in it - but you get the jist

MrMister17
Starting Member

9 Posts

Posted - 2015-04-10 : 15:10:49

Michael,

Worked as I needed -

1 2 3 4 5 6 7 8 9 10 11
04102015 114830 10 013 9 8 6 99999 Test 1 1 0

How do I use this as a Stored Proc ? I need the function or SP to fire everytime I insert the string into a table ? In the example above, I insert raw data into RawData, which needs to be parsed into WIP with your function.

And yes, the number of delimiters will always remain the same.

Thanks,

Mohit.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-13 : 08:36:21

you could do this multiple ways. I would create the procedure to do the insert and parse the data at that time.

this is written as a select , but you can uncomment the insert statement and repoint to your table

CREATE PROCEDURE dbo.SomeProc
@StringToSplit varchar(100)
AS
BEGIN

--INSERT INTO SomeTable -- uncomment and direct toward your table -- for now this will select the data
SELECT [1], [2], [3], [4], [5],[6], [7],

, [9], [10] , [11]
FROM
(SELECT *, 1 A FROM dbo.Split (@StringToSplit,'_')
) p
PIVOT
(
MAX (VALUE)
FOR ID IN
( [1], [2], [3], [4], [5],[6], [7],

, [9], [10] , [11] )
) AS pvt

END
GO
EXEC dbo.SomeProc '04102015_114830_10_013_9_8_6_99999_Test 1_1_0'

if you definitely want to insert after and then parse - you might consider a trigger.

MrMister17
Starting Member

9 Posts

Posted - 2015-04-13 : 10:53:02

Michael,

Trying out the insert gives an error -

Insert into Messages ( PLCData, PLCTime, Plant, Program, Line, Zone, Station, BadgeID, Message, Alarm , Archive)
(SELECT *, 1 A FROM dbo.Split ('04102015_114830_10_013_9_8_6_99999_Test 1_1_0','_')
) p
PIVOT
(
MAX (VALUE)
FOR ID IN
( PLCData, PLCTime, Plant, Program, Line, Zone, Station, BadgeID, Message, Alarm , Archive)
) AS pvt

ERROR -

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'p'.

Thanks,

Mohit.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-13 : 11:17:31

substitute that section of code for this and watch out for the 8 ball

Insert into Messages ( PLCData, PLCTime, Plant, Program, Line, Zone, Station, BadgeID, Message, Alarm , Archive)
SELECT [1] [PLCData], [2] [PLCTime], [3] [Plant], [4] [Program], [5] [Line],[6] [Zone], [7] [Station],

[BadgeID] , [9] [Message], [10] [Alarm], [11] [Archive]
FROM
(SELECT * FROM dbo.Split ('04102015_114830_10_013_9_8_6_99999_Test 1_1_0','_')
) p
PIVOT
(
MAX (VALUE)
FOR ID IN
( [1], [2], [3], [4], [5],[6], [7],

, [9], [10] , [11] )
) AS pvt

MrMister17
Starting Member

9 Posts

Posted - 2015-04-15 : 09:31:20

Thanks Michael. Works like a charm.

MrMister17
Starting Member

9 Posts

Posted - 2015-04-20 : 15:51:04

Michael,

How would I write this as a Trigger to work on an insert ?

Thanks,

Mohit.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-21 : 13:04:56

The trigger would work after the insert, but I would recommend doing this code in the procedure that inserts into the table itself. Handle the data once.

but a trigger would examine the column with the data and update the other columns you are deriving from that string after the insert.

Post the DDL for your table and I can help write a trigger or you can use this as a template

CREATE TABLE [dbo].[t](
[ID] [int] IDENTITY(1,1) NOT NULL,

[varchar](10) NULL
) ON [PRIMARY]

GO

CREATE TRIGGER MyTrigger
ON t
AFTER INSERT
AS
BEGIN
Update T
SET V = 'Triggered'
FROM T INNER JOIN Inserted I ON T.ID = I.ID
END
GO

INSERT INTO [dbo].[t]
VALUES('Green') -- this will be overridden with the word trigger -- you of course will insert the code we have created to update the appropriate columns

SELECT * FROM T

MrMister17
Starting Member

9 Posts

Posted - 2015-04-21 : 15:16:00

Michael,

The data for the RAW table comes from a robot on a manufacturing line. It has to be stored for verification purposes. Therefore, I'd like to run the trigger on insert on this table, parse the string and insert the parsed values to Messages table.

The table for RAW data is -

CREATE TABLE [dbo].[PLCRAWDATA](
[PLCString] [varchar](100) NULL
) ON [PRIMARY]

The table for parsed strings from the table above is -

CREATE TABLE [dbo].[Messages](
[PLCDate] [varchar](10) NOT NULL,
[PLCTime] [varchar](10) NOT NULL,
[Plant] [int] NOT NULL,
[Program] [int] NOT NULL,
[Line] [int] NOT NULL,
[Zone] [int] NOT NULL,
[Station] [int] NOT NULL,
[BadgeID] [int] NOT NULL,
[Message] [varchar](72) NOT NULL,
[Alarm] [int] NOT NULL
) ON [PRIMARY]

SAMPLE DATA -

04/21/2015_09:41:52_10_013_9_8_6_99999_Test 3_1
04/21/2015_09:41:50_10_013_9_8_6_99999_Test 1_1
04/21/2015_09:41:39_10_013_9_8_6_99999_Test 7_1
04/21/2015_09:41:38_10_013_9_8_6_99999_Test 6_1

The string from the RAW table needs to be parsed and inserted to the Messages table -

PLCDate PLCTime Plant Program Line Zone Station BadgeID Message Alarm
04/10/2015 11:48:30 10 13 9 8 6 99999 Test 1 1

Needs a trigger on insert to the RAW Data table to be parsed and inserted into the Messages table.

Thanks,

MOHIT.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-22 : 12:54:28

CREATE TABLE [dbo].[PLCRAWDATA](
[PLCString] [varchar](100) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Messages](
[PLCDate] [varchar](20) NOT NULL,
[PLCTime] [varchar](20) NOT NULL,
[Plant] [int] NOT NULL,
[Program] [int] NOT NULL,
[Line] [int] NOT NULL,
[Zone] [int] NOT NULL,
[Station] [int] NOT NULL,
[BadgeID] [int] NOT NULL,
[Message] [varchar](72) NOT NULL,
[Alarm] [int] NOT NULL,
[Archive] [bit]
) ON [PRIMARY]

GO

CREATE TRIGGER InsertMessage
ON [dbo].[PLCRAWDATA]
AFTER INSERT
AS
BEGIN

INSERT INTO [dbo].[Messages](PLCDate, PLCTime, Plant, Program, Line, Zone, Station, BadgeID, Message, Alarm, [Archive])
SELECT [1] PLCDate, [2] PLCTime, [3] Plant, [4] Program, [5] Line,[6] Zone, [7] Station,

BadgeID, [9] Message, [10] Alarm, [11] [Archive]
FROM
(SELECT *
FROM Inserted I
CROSS APPLY dbo.Split(I.[PLCString],'_') X
) p
PIVOT
(
MAX (VALUE)
FOR ID IN
( [1], [2], [3], [4], [5],[6], [7],

, [9], [10] , [11] )
) AS pvt

END
GO
-- you forgot the archive column and changed the string data , but you should be able to adapt this.

INSERT INTO [dbo].[PLCRAWDATA]
VALUES('04/21/2015_09:41:52_10_013_9_8_6_99999_Test 3_1_1') -- added 11 column, but will work with less if you feed 10 the archive will be null
,('04/21/2015_09:41:50_10_013_9_8_6_99999_Test 1_1_1')
,('04/21/2015_09:41:39_10_013_9_8_6_99999_Test 7_1') -- example with no archive
,('04/21/2015_09:41:38_10_013_9_8_6_99999_Test 6_1_0')

SELECT * FROM [dbo].[Messages]

MrMister17
Starting Member

9 Posts

Posted - 2015-04-23 : 16:23:00

Michael,

That was perfect !

Thanks a lot.

Taking this further -

I am attempting to provide a dynamic report on our intranet - sharepoint using SSRS.
I need to read the table -

CREATE TABLE [dbo].[Messages](
[PLCDate] [varchar](20) NOT NULL,
[Plant] [int] NOT NULL,
[Program] [int] NOT NULL,
[Line] [int] NOT NULL,
[Zone] [int] NOT NULL,
[Station] [int] NOT NULL,
[BadgeID] [int] NOT NULL,
[Message] [varchar](72) NOT NULL,
[Alarm] [int] NOT NULL,
[Archive] [int] NULL
) ON [PRIMARY]

and display a report based on users choice of selection -

Either Date, Plant, Program, Line, Zone, Station, BadgeID, Message.

Sample rows in table are -

PLCDate Plant Program Line Zone Station BadgeID Message
04/23/2015 09:58:50 10 13 09 8 5 9999 Test 1
04/23/2015 09:58:41 09 12 07 8 6 9999 Test 7
04/23/2015 09:58:39 09 12 04 8 6 9999 Test 6
04/23/2015 09:58:37 08 12 04 8 6 9999 Test 5
04/23/2015 09:58:35 07 06 11 8 6 9999 Test 4
04/23/2015 09:58:33 11 03 07 8 6 9999 Test 3
04/23/2015 09:58:30 12 13 03 8 6 9999 Test 2
04/23/2015 09:58:29 01 13 05 8 6 9999 Test 1
04/23/2015 09:58:22 10 01 04 8 6 9999 Test 2

Can anyone help me ?

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-24 : 12:10:58

Here is a procedure - you can just do a report wizard through SSRS

CREATE PROCEDURE ReportOnMessages
@PLCDate varchar(20) = NULL,
@Plant int = NULL,
@Program int = NULL,
@Line int = NULL,
@Zone int = NULL,
@Station int = NULL,
@BadgeID int = NULL,
@Message varchar(72) = NULL

AS
BEGIN

SELECT
[PLCDate],
[Plant] ,
[Program],
[Line] ,
[Zone] ,
[Station],
[BadgeID] ,
[Message] ,
[Alarm] ,
[Archive]
FROM [dbo].[Messages]
WHERE
([PLCDate] =@PLCDate OR @PLCDate IS NULL ) AND
([Plant] = @Plant OR @Plant IS NULL ) AND
([Program] = @Program OR @Program IS NULL ) AND
([Line] = @Line OR @Line IS NULL ) AND
([Zone] = @Zone OR @Zone IS NULL ) AND
([Station] = @Station OR @Station IS NULL ) AND
([BadgeID] = @BadgeID OR @BadgeID IS NULL ) AND
([Message] = @Message OR @Message IS NULL )

END

MrMister17
Starting Member

9 Posts

Posted - 2015-04-24 : 12:57:20

Michael,

How do I pass parameters in the SSRS report. I have a standalone report created which I can populate all the rows. Your procedure passes the specific string the user needs as a criteria for search, but how do I incorporate that into the report ?

I need the user to select either of the fields from a drop down to pass as a search criteria ?

Thanks,

Mohit.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-24 : 13:13:02

I'm not sure of your skill level with SSRS, but if you walk through a new project using report wizard - you should be able to do this very easily

create a new SSRS project using report wizard
set up your data source by selecting your instance and db
when you get to the query builder - past the following
EXEC ReportOnMessages
@PLCDate = NULL,
@Plant = NULL,
@Program = NULL,
@Line = NULL,
@Zone = NULL,
@Station = NULL,
@BadgeID = NULL,
@Message= NULL

just click next all the way through and finish -
you will have a new report with the parameters set up
on the left - you will see the report data window - if not open it.
expand the parameter section and right click on each parameter to set the properties -- you will want to make sure each allows you to pass null.

then - there you go.

you can now pass any number or no parameters

here is a quick link
http://www.c-sharpcorner.com/UploadFile/db2972/create-ssrs-report-using-report-wizard/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources