Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Unanswered: Criteria; Search any word in field

    I am trying to set up a query that will allow the user to input a string, and the search will match ANY word in that string. Currently, I have it configured so that the search will only match the exact string that the user inputs. I have google searched for the answer, but no luck yet. Any ideas?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    where @YourInputString like "%" + [YourDataValue] + "%"


    ...and no, this will not be very fast.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2004
    Posts
    7
    blindman, thanks for the reply. Here is my sql code, but I could not get your suggestion to work.

    SELECT [Lessons Learned Table].[Tester ID], [Lessons Learned Table].Name, [Lessons Learned Table].Comment, [Lessons Learned Table].[Suggested Solution]
    FROM [Lessons Learned Table]
    WHERE ((([Enter Your Name]) Like "%"+[Lessons Learned Table].Name+"%"));

    Can you see my error? Thanks,

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't think that's what your asking....

    I think it's this....but why the hell is NOCOUNT OM not working?

    Code:
    USE Northwind
    GO
    SET NOCOUNT ON 
    GO
    
    CREATE PROC mySproc99 @SearchString varchar(255)
    AS
    SET NOCOUNT ON
    DECLARE @x Table (Col1 varchar(255))
    DECLARE @y int, @z int
    SELECT @y = CHARINDEX(' ',@SearchString), @z = 1
      BEGIN
    	WHILE @y <> 0
    	  BEGIN
    		INSERT INTO @x(Col1) 
    		SELECT SUBSTRING(@SearchString,@z,(@y-1)-@z+1)
    		SELECT @z = @y + 1
    		SELECT @y = CHARINDEX(' ',@SearchString,@z)
    	  END
    	INSERT INTO @x(Col1) 
    	SELECT RIGHT(@SearchString,(LEN(@SearchString)-@z)+1)
    
    	    SELECT * 
    	      FROM Products 
    	INNER JOIN @x 
    		ON ProductName LIKE '%'+Col1+'%'
      END
    SET NOCOUNT OFF
    GO
    
    SET NOCOUNT OFF
    GO
    
    SET NOCOUNT ON
    EXEC mySproc99 'Beer Lager Ale Stout'
    SET NOCOUNT OFF
    GO
    
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Umm, what is [Enter Your Name]?

    If it is a column in your [Lessons Learned Table] then I don't get you query at all. If it is a variable then it needs to be preceded with the @ sign: @EnterYourName

    What error or behavior are you getting?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2004
    Posts
    7
    When I am editing my query in design view, in the criteria field I have: [Enter your Name:]. When the query is run a pop up box will show asking "Enter your Name:". When the user enters a name and clicks OK the query will search for that name in the name field in the Lessons Learned Table. That works fine except if the user enters: Joe Larry Brian then the query returns nothing even if a Joe, Larry and Brian exist in the Name field. What I need is for the query to search for each individual name seperatly in the Name field. I hope this is a better explanation of what I am trying to accomplish. Thanks,

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What interface are you using that brings up a pop-up box?

    I am confused about your application architecture, and I suspect that may be where some of your problems are coming from.

    So [Enter Your Name] may contain more than one name, such as "Joe Larry Brian", and you want it to hit against a record with a [Name] value of "Larry"?

    The code I gave you should work for this.
    ----------------------------------------------------
    set nocount on
    create table LessonsLearned ([Name] varchar(50))

    insert into LessonsLearned ([Name]) select 'Curly' UNION select 'Moe' UNION select 'Larry' UNION select 'George W'

    select * from LessonsLearned where 'Curly Shemp Larry' like '%' + [Name] + '%'

    drop table LessonsLearned
    ------------------------------------------------------
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Very slick....never thought of it that way....

    Nice scan....

    I betcha it's access we're dealing with....

    WHERE [Please Enter your Opinion] = Col1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I doubt there is any way to get this to use an index...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I was messing around with

    Code:
    	    SELECT * 
    	      FROM Products 
    	INNER JOIN @x 
    --		ON ProductName LIKE '%'+Col1+'%'
    		ON CHARINDEX(ProductName,Col1) <> 0
    But it don't work....stage 2 anyway? Not sure....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •