-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: SriSamp
Message 13 in Discussion
I have exactly the same table and it works fine! 44,55 says that is already exists.
Check if you are running the correct code. I'm pasting it here again: IF (OBJECT_ID
('dbo.GetPatternCount') IS NOT NULL)
DROP FUNCTION dbo.GetPatternCount
GO
CREATE FUNCTION dbo.GetPatternCount
(
@tcInput VARCHAR(11),
@tcDelimiter VARCHAR(1)
) RETURNS INT AS
BEGIN
DECLARE @delimiter INT, @Count INT SELECT @delimiter = 1, @Count = 1
WHILE (@delimiter > 0)
BEGIN
SET @delimiter = CHARINDEX (@tcDelimiter, @tcInput)
IF (@delimiter > 0)
BEGIN
SET @Count = @Count + 1
SET @tcInput = SUBSTRING (@tcInput, @delimiter+1, LEN (@tcInput)[EMAIL PROTECTED])
END
END
RETURN (@Count)
END
GO
SELECT * FROM table_x
GO
DECLARE @patCount INT, @found INT
DECLARE @field_1 VARCHAR(3), @field_2 VARCHAR(11)
DECLARE @delimiter VARCHAR(1), @searchFor VARCHAR(11), @searchString VARCHAR(10),
@searchFor_1 VARCHAR(11)
BEGIN
SET NOCOUNT ON -- Sample input string. Change this to test the program
SET @searchFor = '44,56' -- Initialize the variables
SELECT @found = 0, @delimiter = 1 -- Split the given input string based on the
delimiter and
-- find the number of inputs
SET @patCount = dbo.GetPatternCount (@searchFor, ',') -- From the table get only
those records that have the
-- same number of chunks as that of the input
DECLARE myCursor CURSOR FOR
SELECT field_1, field_2 FROM table_x
WHERE dbo.GetPatternCount(field_2, ',') = @patCount -- Make a backup of the search
string
SET @searchFor_1 = @searchFor -- Open the cursor and loop through the records
OPEN myCursor
FETCH NEXT FROM myCursor INTO @field_1, @field_2
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Strip the input based on the delimiter and for each chunk
-- found, search whether it exists in the current cursor record
WHILE (@delimiter > 0)
BEGIN
SET @delimiter = CHARINDEX (',', @searchFor_1)
IF (@delimiter <> 0)
BEGIN
SET @searchString = SUBSTRING (@searchFor_1, 1, @delimiter-1)
SET @searchFor_1 = SUBSTRING (@searchFor_1, @delimiter+1, LEN (@searchFor_1)[EMAIL
PROTECTED])
END
ELSE
SET @searchString = @searchFor_1 -- Check for the presence in the current
cursor record
IF (CHARINDEX (',' + @searchString + ',', ',' + @field_2 + ',') <> 0)
SET @found = @found + 1
ELSE
SET @found = @found - 1
END IF (@found = @patCount)
BREAK -- Reset values for the next iteration and fetch the next
-- cursor record
SELECT @searchFor_1 = @searchFor, @found = 0, @delimiter = 1
FETCH NEXT FROM myCursor INTO @field_1, @field_2
END
CLOSE myCursor
DEALLOCATE myCursor IF (@found = @patCount)
SELECT 'Pattern already exists. Cannot insert the given value'
ELSE
SELECT 'Pattern does not exist and can be inserted' SET NOCOUNT OFF
END
Make sure that you run the function code first and after that you should run only the
logic code. HTH, Srinivas Sampath MVP - SQL Server
http://www32.brinkster.com/srisamp
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/BDotNet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you received
this message by mistake, please click the "Remove" link below. On the pre-addressed
e-mail message that opens, simply click "Send". Your e-mail address will be deleted
from this group's mailing list.
mailto:[EMAIL PROTECTED]
