This TSQL SCRIPT makes it easier to perform a AND/OR search. When using this method you don't have to split any text up in your code, and it will be easier to use a stored procedure for your database searches. I have pasted the code below [pretty self-explanatory :)]
-- SEARCH STRING
DECLARE @SearchWords varchar(255)
SELECT @SearchWords = 'This is a test anyone can use it to make searches, it is recommended for stored procedures.'
SELECT @SearchWords = @SearchWords + ' It will automatically strip white spaces.'
SELECT @SearchWords = @SearchWords + ' When doing a search I would recommend using AND clause between words.'
-- TRIM SEARCH STRING (no white spaces b
-- efore and after)
SET @SearchWords = LTRIM(RTRIM(@SearchWords))
-- DECLARE TEMPERARY VARIABLES
DECLARE @TempWord varchar(55)
DECLARE @Delimiter char(1)
DECLARE @patIndex int
-- CREATE TEMPERARY TABLE FOR SEARCHWORD
-- S
CREATE TABLE #SearchWords (Word varchar(255))
-- SET ATART VALUES
SELECT @patIndex = 1
SELECT @Delimiter = ' '
-- INSERT DELIMITER IN END OF STRING TO
-- MAKE SURE WE ALSO GET THE LAST WORD
SELECT @SearchWords = @SearchWords+@Delimiter
While (@patIndex > 0)
BEGIN
-- FIND FIRST OCCURENCE OF DELIMITER
SET @patIndex = PATINDEX('%' + @Delimiter + '%',@SearchWords)
IF @patIndex = 0
BEGIN
BREAK
END
-- GET THE WORD (WORD BEFORE DELIMITER)
SET @TempWord = SUBSTRING(@SearchWords,0,@patIndex)
-- EXCLUDE THE WORD (WORD BEFORE DELIMITER) FROM SEARCH STRING
SET @SearchWords = SUBSTRING(@SearchWords,@patIndex+1,LEN(@SearchWords))
IF @TempWord != ''
BEGIN
INSERT INTO #SearchWords(word) VALUES (@TempWord)
END
END
SELECT * FROM #SearchWords
DROP TABLE #SearchWords
| 
