Split Tokens into an Array - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Split Tokens into an Array

Author

Topic

FailSafe
Starting Member

2 Posts

Posted - 2003-10-03 : 21:29:25

I pass 4 Variables to the Procedure and the following SHOULD happen.

If @MsgGUID does not exist in the MESSAGES Table then the it should be INSERTed, if it does exist then it should be updated.
@Tokens is a delimited String which is parsed to the Function and each element of the array is checked for existence in the TOKENS Table. If the Token does exist then id @IsSpam is TRUE then the TOKENS.Spams Column is incremented by ONE else the TOKENS.Hams Column is incremented by ONE. If the Token does NOT exist then it is added to the TOKENS Table and the appropriate Column is set to ONE or ZERO (Spams / Hams)
Finally the Score is calculated and written to the TOKENS.Score Column.

I know this is a big ask but I really need this functionality and I am a NOVICE at SQL.

The 2 Tables have the following Columns

Messages
------------
MsgGUID nVarChar(32) - I allocate this GUID Manually and is the Key
Spam bit(1) True if Spam or False if not Spam

Tokens
---------
Token nVarChar(50) - Key Column
Spams Int(4)
Hams Int(4)
Score decimal(5)


-- --------------------------------------------------
-- CREATE PROCEDURE UPDATE TOKENS
-- --------------------------------------------------
CREATE PROCEDURE spUpdateTokens
(
	@MsgGUID	nVarChar(32),
	@Tokens		nVarChar(4000),
	@Delimiter	Char(1),
	@IsSpam		Bit
)
AS
SET NoCount ON
SET Xact_Abort ON
DECLARE	@GoodMessages	int,
	@BadMessages	int
BEGIN TRANSACTION 
	If @IsSpam = 1
		BEGIN
			UPDATE Messages
			SET Spam = 1
			WHERE MsgGUID = @MsgGUID

			UPDATE T
			SET T.Spams = T.Spams + 1
			FROM Tokens T
			INNER JOIN fnSplitString(@Tokens, @Delimiter) F
			ON T.Token = F.Token

			INSERT Tokens (Token, Spams)
			SELECT F.Token, 1
			FROM fnSplitString(@Tokens, @Delimiter) F
			LEFT OUTER JOIN Tokens T
			ON F.Token = T.Token
			WHERE T.Token is null
		END

	If @IsSpam = 0
		BEGIN
			UPDATE Messages
			SET Spam = 0
			WHERE MsgGUID = @MsgGUID

			UPDATE T
			SET T.Hams = T.Hams + 1
			FROM Tokens T
			INNER JOIN fnSplitString(@Tokens, @Delimiter) F
			ON T.Token = F.Token

			INSERT Tokens (Token, Hams)
			SELECT F.Token, 1
			FROM fnSplitString(@Tokens, @Delimiter) F
			LEFT OUTER JOIN Tokens T
			ON F.Token = T.Token
			WHERE T.Token is null

		END
	SELECT @GoodMessages = COUNT(*) 
	FROM Messages
	WHERE Spam = 0

	SELECT @BadMessages = COUNT(*)
	FROM Messages
	WHERE Spam = 1

	If (@GoodMessages > 0 and @Badmessages > 0)
		BEGIN
			UPDATE Tokens
			SET Score = Convert(decimal(6,3), Spams) / Convert(decimal(6,3), @BadMessages) /
			(
				Convert(decimal(6,3), Hams) / Convert(decimal(6,3), @GoodMessages) + 
				Convert(decimal(6,3), Spams) / Convert(decimal(6,3), @BadMessages)
			)
		END
COMMIT
GO


This function appears to work perfectly
-- ---------------------------------------------------
-- SPLIT FUNCTION - Splits a String using a Delimiter
-- ---------------------------------------------------
CREATE FUNCTION fnSplitString
(
	@InString nvarchar(4000),
	@Delimiter char(1)
)

RETURNS	@Tokens table
	(
		Token nVarChar(50)
	)
AS
BEGIN

	DECLARE @Position int

	WHILE Len(@InString) > 0
		BEGIN
			SET @Position = CharIndex(@Delimiter, @InString)
			If @Position > 0
				BEGIN
					INSERT	@Tokens
					SELECT	LTrim(RTrim(Left(@InString, @Position - 1)))
					SET	@InString = Right(@InString, Len(@InString) - @Position)
				END
			ELSE
				BEGIN
					INSERT	@Tokens
					SELECT	LTrim(RTrim(@InString))
					SET	@InString = ''
				END
		END
RETURN
END

Brian Gillham
FailSafe Systems

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-04 : 07:20:47

Seems you should insert into your code and right after
the BEGIN TRANSACTION these two lines:

BEGIN TRANSACTION
if not exists(select 0 from Messages where MsgGUID=@MsgGUID)
insert into Messages (MsgGUID, Spam) values (@MsgGUID, @IsSpam)

FailSafe
Starting Member

2 Posts

Posted - 2003-10-04 : 15:18:42

Thanks for the reply, I will try it and see what the result is.

Brian Gillham
FailSafe Systems

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources