SQL WHILE loop inside WHERE clause

Question

Can I put a WHILE loop inside WHERE clause? I have a stored procedure where I'm trying to put in text searching capability. I have it working for an exact match like this:

AND (@exactString = '' 
    OR (CHARINDEX(@exactString, [Short Description]) > 0 
        OR CHARINDEX(@exactString, [Description]) > 0
        OR CHARINDEX(@exactString, [Manufacturer]) > 0))

Next I'm trying to do a "any word" match and an "all words" match. I can get the search string I want to search for with the following WHILE loop:

DECLARE @searchString varchar(max)
DECLARE @endIndex int

SET @allString = LTRIM(RTRIM(@allString))
WHILE LEN(@allString) > 0
BEGIN
    SET @endIndex = CHARINDEX(' ', @allString) > 0
    IF @endIndex > 0
    BEGIN
        SET @searchString = LEFT(@allString, @endIndex)
        SET @allString = LTRIM(RTRIM(RIGHT(@allString, @endIndex)))
    END
ELSE
    BEGIN
        SET @searchString = @allString
        SET @allString = ''
    END
END

Now I want to use the @searchString variable like I used @exactString above. Is there a way to do this inside my loop or is there some other technique I'm missing that would work here?

Thanks for your help, Dan

@swasheck - The first time I ran across full-text search in researching this the post said "assuming you are on SQL 2008" which we're not (2005) so I assumed that wouldn't work for me. — Dan Norton
– Dan Norton, Commented Aug 10, 2012 at 12:12
Full-text search is available for 2005. I'll have to find a link — swasheck
– swasheck, Commented Aug 10, 2012 at 13:19

Michael Erickson · Accepted Answer · 2012-08-09 23:11:17Z

I have used a table value function to perform this task using a query such as the following:

SELECT I.*
  FROM @info AS I
        INNER JOIN dbo.funcSplitToTable( ' ', @allString ) AS S
                ON I.[Manufacturer] LIKE '%' + S.result + '%'
                OR I.[Description] LIKE '%' + S.result + '%'
                OR I.[Short Description] LIKE '%' + S.result + '%'

This table value function is defined as follows:

CREATE FUNCTION dbo.funcSplitToTable
/*
   Split a string into parts base on a separation character to produce
   a table that has one column containing the results of the split.

   EXAMPLE:
      SELECT * FROM dbo.funcSplitToTable( '~', 'MAINT~12221~10001~10/25/2004~CANCELLED~1' )
      SELECT * FROM dbo.funcSplitToTable( '~', '' )
      SELECT * FROM dbo.funcSplitToTable( '~', NULL )
      SELECT * FROM dbo.funcSplitToTable( NULL, 'MAINT~12221~10001~10/25/2004~CANCELLED~1' )
      SELECT * FROM dbo.funcSplitToTable( '', 'MAINT~12221~10001~10/25/2004~CANCELLED~1' )

   RETURN:
      Table with one column containing resulting strings.
*/
(
    @strSearch       AS varchar(255)            -- String to search for.
   ,@strText         AS varchar(MAX )           -- Text to search for string.
)
RETURNS @tblResult TABLE (
   result varchar(MAX) NOT NULL
)
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @iLastPos    int
   DECLARE @iPos        int
   DECLARE @strResult   varchar(MAX)

   IF @strText IS NULL RETURN ;
   IF @strSearch IS NULL SET @strSearch = '' ;

   SET @strResult    = NULL ;
   SET @iLastPos     = 1 ;
   SET @iPos         = CHARINDEX( @strSearch, @strText ) ;

   WHILE @iPos > 0
   BEGIN
      IF (@iPos - @iLastPos) > 0
          INSERT INTO @tblResult
          SELECT SUBSTRING( @strText, @iLastPos, (@iPos - @iLastPos) ) AS result
          ;
      SET @iLastPos  = @iPos + 1 ;
      SET @iPos      = CHARINDEX( @strSearch, @strText, @iLastPos ) ;
   END

   IF (1 + LEN(@strText) - @iLastPos) > 0
       INSERT INTO @tblResult
       SELECT SUBSTRING( @strText, @iLastPos, (1 + LEN(@strText) - @iLastPos) ) AS result
       ;
   RETURN ;
END

This totally did the trick for the "any" search. I already had a CLR_charlist_split function (from sommarskog.se/arrays-in-sql-2005.html), which I used to split to table, but doing it with a join and LIKE hadn't occurred to me. Thanks!

Dan Norton · Accepted Answer · 2012-08-10 15:55:58Z

0

I got a great answer from Michael Erickson that totally works for the "any" search. For the "all" search. I built up an sql string with the entire query. The "all" search section is here:

IF LEN(@allString) > 0
BEGIN
    DECLARE @searchString varchar(max)
    DECLARE @endIndex int
    DECLARE @isFirstString bit

    SET @isFirstString = 0
    SET @allString = LTRIM(RTRIM(@allString))
    WHILE LEN(@allString) > 0
    BEGIN
        SET @endIndex = CHARINDEX(' ', @allString)
        IF @endIndex > 0
            BEGIN
                SET @searchString = LTRIM(RTRIM(LEFT(@allString, @endIndex)))
                SET @allString = LTRIM(RTRIM(RIGHT(@allString, LEN(@allString) - @endIndex)))
            END
        ELSE
            BEGIN
                SET @searchString = @allString
                SET @allString = ''
            END
        SET @sql = @sql + ' AND ((CHARINDEX(''' + cast(@searchString as varchar(max)) + ''', [Short Description]) > 0 
            OR CHARINDEX(''' + cast(@searchString as varchar(max)) + ''', [Description]) > 0
            OR CHARINDEX(''' + cast(@searchString as varchar(max)) + ''', [Manufacturer]) > 0))'
    END
END
EXEC (@sql)

Thanks again, Dan

edited Aug 10, 2012 at 15:55

answered Aug 10, 2012 at 15:24

Dan Norton

3621 gold badge5 silver badges17 bronze badges

1 Comment

Tony Over a year ago

Would you be willing to share some information on the speed of this query (and the one Michael gave you)? I'd love to know how many records you're searching and the time to return a result. I'm not disparaging the query, I'm just really interested to know.

Collectives™ on Stack Overflow

SQL WHILE loop inside WHERE clause

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related