Need trigger to split 1 filed to 4
-
Hi
I have been desperately looking for a way to do this. I need to write a Trigger on insert that will take a field and split the value in it into 6 other fields in the same table, if possible.
The value is comma delimeted and all parts of it will be variable lengths.
Thanx in advance
-
You may post some samples here.
-
I don't know how to do this, that's why I am looking for help.
My table is called SMS and contain fields ID, Message(varchar), part1(varchar), part2(varchar), part3(varchar, part4(varchar). When I insert a new record, I want a trigger to take the value in the message field and split it into the part1 to part 4 fields.
The value in die message field will be something like 37,Text,200,90
Please help if you know how to do this
-
This is a tough one.
I have started something, (using CHARINDEX), but getting past Part2 is proving difficult for me. I do not write that many Triggers. Can a stored procedure be called from a Trigger? Or, can @Variables be used in a Trigger?
Thanks folks...
I wasn't born stupid - I had to study.
-
I know this script uses a cursor, which is not ideal within a trigger, but I think you will find that it should work.....
create trigger SMS_ITrig on SMS
for insert
as
begin
declare @ID int,
@message varchar(1000),
@p1 varchar(250),
@p2 varchar(250),
@p3 varchar(250),
@p4 varchar(250),
@delimiter char(1),
@pos int
set @delimiter = ','
declare msg cursor for
select ID, Message
from inserted
open msg
fetch next from msg into @ID, @Message
while @@fetch_status = 0
begin
-- extract part1 value
set @pos = charindex(@delimiter, @message)
if @pos != 0
begin
set @p1 = left(@message, @pos - 1)
set @message = right(@message, len(@message) - @pos)
end
else
set @p1 = null
-- extract past2 value
set @pos = charindex(@delimiter, @message)
if @pos != 0
begin
set @p2 = left(@message, @pos - 1)
set @message = right(@message, len(@message) - @pos)
end
else
set @p2 = null
-- extract part3 value
set @pos = charindex(@delimiter, @message)
if @pos != 0
begin
set @p3 = left(@message, @pos - 1)
set @message = right(@message, len(@message) - @pos)
end
else
set @p3 = null
-- extract part 4 value
if len(@message) != 0
set @p4 = @message
else
set @p4 = null
-- update record
update SMS
set part1 = @p1,
part2 = @p2,
part3 = @p3,
part4 = @p4
where ID = @ID
fetch next from msg into @ID, @Message
end
close msg
deallocate msg
end
go
-
Here's a method that uses a user-define function (UDF). First, create the UDF dbo.fGetToken(). I used the following as a test. I've commented out the DROP TABLE statement. This may not be the best method if you insert large numbers of rows per batch. However, the advantage is that the trigger code itself is simple and readable.
CREATE FUNCTION dbo.fGetToken
(
@parm varchar(8000),
@delim varchar(100),
@whichOccur smallint
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @occur int, @spos int, @startPos int, @maxlen int
DECLARE @token varchar(8000)
DECLARE @delimLen int
-- Len() function ignores trailing blanks, so if our delimiter is a space,
-- or multiple spaces, Len() will returns zero.
IF Len(@delim) = 0 AND DataLength(@delim) > 0
SET @delimLen = DataLength(@delim) -- DataLength(@delim) * 2 for nvarchar
ELSE
SET @delimLen = Len(@delim)
SET @occur = 0
SET @startPos = 1
SET @spos = 1 - @delimLen
SET @maxLen = Len(@parm)
WHILE @occur < @whichOccur AND @parm <> '' AND @parm IS NOT NULL AND @startPos < @maxLen
BEGIN
SET @occur = @occur + 1
SET @startPos = @spos + @delimLen
SET @spos = CHARINDEX( @delim , @parm, @startPos )
IF @spos = 0
BREAK
END
IF @occur <> @whichOccur
SET @token = '' -- or NULL
ELSE
IF @spos = 0
SET @token = Substring(@parm, @startPos, 8000)
ELSE
SET @token = SubString( @parm, @startPos, @spos - @startPos)
RETURN @token
END
GO
-- DROP TABLE SMS
GO
CREATE TABLE SMS
(
id int PRIMARY KEY IDENTITY(1,1)
, message varchar(100) NOT NULL
, part1 varchar(100) NULL
, part2 varchar(100) NULL
, part3 varchar(100) NULL
, part4 varchar(100) NULL
)
GO
CREATE TRIGGER tri_SMS
ON SMS
FOR INSERT
AS
UPDATE SMS
SET part1 = dbo.fGetToken(i.message, ',', 1)
, part2 = dbo.fGetToken(i.message, ',', 2)
, part3 = dbo.fGetToken(i.message, ',', 3)
, part4 = dbo.fGetToken(i.message, ',', 4)
FROM SMS JOIN inserted i ON SMS.ID = i.ID
GO
-- Test the trigger
SET NOCOUNT ON
INSERT SMS (message) VALUES ( '37,Text,200,90' )
INSERT SMS (message) VALUES ( '123,This is a longer message. ,111,10' )
INSERT SMS (message) VALUES ( '234,This message goes with 234,222,20' )
INSERT SMS (message) VALUES ( '345,This message goes with 345,333,30' )
INSERT SMS (message) VALUES ( '456,This message goes with 456,444,40' )
SET NOCOUNT OFF
SELECT * FROM SMS
GO
-
Here is yet another possibility. Since the other posts taught me you can use @Variable declaration within a Trigger, I used the following approach. Three major things: One, I assumed a varchar(80) would be large enough to hold the Message. Two, I did not code for any NULLs. There, I did not know if any of these fields needs to be converted to another datatype.
Basically, this adds a comma to the end of the Message and puts it into a variable [@Message]. It then finds the location of the first comma and gets the string behind that location. Part1 is then updated.
The next section gets the string starting from the position @Location to the end and makes that the variable @Message. Hence, the first section, (what was updated for Part1) is removed. It then follows the same method used to update Part1 and updates Part2, Part3, and Part4.
I did not know your data well enough to have a good constraint, (i.e., the WHERE clause), so you would need to look further into this... Hopefully you will find this useful and may well improve upon it....
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[SMSSplitMessage]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[SMSSplitMessage]
GO
CREATE TRIGGER SMSSplitMessage
ON dbo.SMS
FOR AFTER INSERT
AS
BEGIN
DECLARE @Message varchar(81),
@Location integer
SELECT @Message = (SELECT Message + CHAR(44) FROM SMS WHERE Part1 IS NULL)
SELECT @Location = (SELECT CHARINDEX( CHAR(44), Message, 1) - 1) FROM SMS WHERE Part1 IS NULL)
BEGIN TRANSACTION Part1Update
UPDATE SMS SET
Part1 = SELECT SUBSTRING( Message, 1, @Location)
FROM SMS
WHERE Part1 IS NULL
COMMIT TRANSACTION Part1Update
SELECT @Message = (SELECT SUBSTRING( @Message, @Location + 2, 80)
SELECT @Location = (SELECT CHARINDEX( CHAR(44), Message, 1) - 1) FROM SMS WHERE Part1 IS NULL)
BEGIN TRANSACTION Part2Update
UPDATE SMS SET
Part2 = SELECT SUBSTRING( Message, 1, @Location)
FROM SMS
WHERE Part2 IS NULL
COMMIT TRANSACTION Part2Update
SELECT @Message = (SELECT SUBSTRING( @Message, @Location + 2, 80)
SELECT @Location = (SELECT CHARINDEX( CHAR(44), Message, 1) - 1) FROM SMS WHERE Part1 IS NULL)
BEGIN TRANSACTION Part3Update
UPDATE SMS SET
Part3 = SELECT SUBSTRING( Message, 1, @Location)
FROM SMS
WHERE Part3 IS NULL
COMMIT TRANSACTION Part3Update
SELECT @Message = (SELECT SUBSTRING( @Message, @Location + 2, 80)
SELECT @Location = (SELECT CHARINDEX( CHAR(44), Message, 1) - 1) FROM SMS WHERE Part1 IS NULL)
BEGIN TRANSACTION Part4Update
UPDATE SMS SET
Part4 = SELECT SUBSTRING( Message, 1, @Location)
FROM SMS
WHERE Part4 IS NULL
COMMIT TRANSACTION Part4Update
END
I wasn't born stupid - I had to study.
-
Here is a different take than above. It did use charindex as suggested above and len to find the starting point length of the part for each of the 4 parts. I will not deal with anything above the 4 parts and since you didn't specify what lengths we were dealing with I just guessed.
CREATE TRIGGER SMSInsert ON [dbo].[SMS]
FOR INSERT
AS
declare @Mess varchar (200)
declare @p1 varchar (50), @p2 varchar(50), @p3 varchar(50), @p4 varchar(50)
declare @i int
set @i = 1
set @Mess = (select message from inserted)
set @p1 = substring(@mess,@i, (charindex(',',@mess,@i)-1 ))
set @i = charindex(',',@mess,@i) +1
set @p2 = substring(@mess,@i, (charindex(',',@mess,@i)- (@i) ))
set @i = charindex(',',@mess,@i) +1
set @p3 = substring(@mess,@i, (charindex(',',@mess,@i)- (@i) ))
set @i = charindex(',',@mess,@i) +1
set @p4 = substring(@mess,@i, (len(@mess) - @i +1))
UPDATE SMS
SET Part1 = @p1,
Part2 = @p2,
Part3 = @p3,
Part4 = @p4
FROM inserted
WHERE SMS.pkey = inserted.pkey
good luck!
-
I am afraid that your solution does not take account of the fact that when a trigger fires it can relate to a set of data (i.e. more than one row). Your code would only work with updates to a single row of data.
-
I think Judith's code would work for each individual Insert - Tertius may know of further constraints to apply. It looks like a nice, clean use of CharIndex...
As for mine, I noted that further, more specific constraints would be necessary. I wasn't born stupid - I had to study.
-
My last post relates specifically to Judith solution, but in hind sight Farrells solution falls foul of the same problem since the @location and @message variables to not take account of multiple records.
-
Here is yet another way.
--DROP TABLE SMS
GO
CREATE TABLE SMS
(
id int PRIMARY KEY IDENTITY(1,1)
, message varchar(100) NOT NULL
, part1 varchar(100) NULL
, part2 varchar(100) NULL
, part3 varchar(100) NULL
, part4 varchar(100) NULL
)
GO
CREATE TRIGGER tri_SMS
ON SMS
FOR INSERT
AS
UPDATE SMS
SET part1 = Left( SMS.message, CHARINDEX( ',' , SMS.message, 1 ) - 1 )
, part2 = SubString( SMS.message, CHARINDEX( ',' , SMS.message, 1 ) + 1, 8000 )
FROM SMS JOIN inserted i ON SMS.ID = i.ID
UPDATE SMS
SET part2 = Left( SMS.part2, CHARINDEX( ',' , SMS.part2, 1 ) - 1 )
, part3 = SubString( SMS.part2, CHARINDEX( ',' , SMS.part2, 1 ) + 1, 8000 )
FROM SMS JOIN inserted i ON SMS.ID = i.ID
UPDATE SMS
SET part3 = Left( SMS.part3, CHARINDEX( ',' , SMS.part3, 1 ) - 1 )
, part4 = SubString( SMS.part3, CHARINDEX( ',' , SMS.part3, 1 ) + 1, 8000 )
FROM SMS JOIN inserted i ON SMS.ID = i.ID
GO
-- Test the trigger
SET NOCOUNT ON
INSERT SMS (message) VALUES ( '37,Text,200,90' )
INSERT SMS (message) VALUES ( '123,This is a longer message. ,111,10' )
INSERT SMS (message) VALUES ( '234,This message goes with 234,222,20' )
INSERT SMS (message) VALUES ( '345,This message goes with 345,333,30' )
INSERT SMS (message) VALUES ( '456,This message goes with 456,444,40' )
SET NOCOUNT OFF
SELECT * FROM SMS
GO
-
Check out the Parsename function. If you always have four pieces of data they can be retrieved by replacing the delimiter with a "."
parsename(replace(fieldname,',','.'),4)
parsename(replace(fieldname,',','.'),3)
parsename(replace(fieldname,',','.'),2)
parsename(replace(fieldname,',','.'),1)
not the intended use of the function but works for your example
-
That is VERY cool! Thanks!
I have not seen that function...
I wasn't born stupid - I had to study.
-
Farrell
Judiths code would not work if there are more than a single row inserted (i.e. by using an INSERT.... SELECT.... statement).
The part of the code which SET's all the parameters would only be executed once for the whole set of rows and not fire for each row in the inserted table.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply