1

I have a question regarding some SQL statement. I search for a name, e.x. "Anne Marie Parker". This search query I pass over to my script.

The script uses the following sql code:

SELECT * FROM table WHERE name LIKE '%(searchquery)%'

(searchquery) will be the name I am looking for.

The issue is the following. I not only want to get Anne Marie Parker as a result, I also want to get results with "Anne Parker".

Is there any possibility to do this in my sql query? I could prepare it by code, but I want it to be done in my sql query.

Is there some kind of function like replace all spaces with "%" which will then be interpreted by the regex?

Thank you very much and have a nice day!

Bye, WorldSignia

Improve this question
3
  • 1
    There are often functions such as str_replace or regexp_replace that can do this, but the details vary between vendors, so we'll need to know what database (Oracle, MySQL, PostgreSQL,...) before we can answer for sure. Look up "string replacemente" in your DB manual if you can. Commented Nov 24, 2010 at 18:02
  • Are you using a stored procedure to pass the parameters ? Commented Nov 24, 2010 at 18:04
  • Why don't you search by something like 'Anne%Parker'? Ex: SELECT * FROM table WHERE name LIKE '%Anne%Parker%' this returns what you want Commented Nov 24, 2010 at 18:33

6 Answers 6

Reset to default
1

replacing the spaces with wildcards won't really help here. it seems like what you really want is to split the values on the spaces, and create a query based on the resultant table. this is the function i use to parse delimited strings:

CREATE FUNCTION [dbo].[fn_Utility_ParseDelimitedString] 
    (
        @String varchar(8000), 
        @Delimeter varchar(5)
    )
    RETURNS 
    @ParsedString TABLE 
    (
        StringVal varchar(2000)
    )
    AS
    BEGIN

    declare @pos int
    declare @piece varchar(500)

    -- Need to tack a delimiter onto the end of the input string if one doesn't exist
    if right(rtrim(@string),1) <> @Delimeter
     set @string = @string  + @Delimeter

    set @pos =  patindex('%,%' , @string)
    while @pos <> 0
    begin
     set @piece = left(@string, @pos - 1)

     -- You have a piece of data, so insert it, print it, do whatever you want to with it.
     INSERT INTO @ParsedString VALUES(cast(@piece as varchar(2000)))

     set @string = stuff(@string, 1, @pos, '')
     set @pos =  patindex('%'+ @Delimeter +'%' , @string)
    end

    RETURN 
END

then your query would look something like this:

select * 
from table 
     inner join fn_utility_parsedelimitedstring(@searchquery,' ') list 
       on table.name like '%'+list.Stringval+'%'

i think something like that will get you a more full-text search feel. this has not really been tested, so ymmv

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

+1 if only for answering the question asked. But I can't stop myself from mentioning that it's "delimiter" (sorry).
@Stuart Pegg, ha, its funny that i got it right everywhere but the parameter. i actually just rigged this up from a comma separated one that i had lying around, so i'd be surprised if that was all i missed.
0

Here is how I would do it.

DECLARE @T VARCHAR(340)
SET @T ='Anne Parker'
SELECT @T = 'SELECT * FROM CUSTOMERS WHERE FULLNAME LIKE ''%' + REPLACE(@T,' ','%') + '%'' ORDER BY FULLNAME'
EXEC (@T);
Improve this answer

Comments

0 
select
  *
from
  table
where
  name like "%anne marie parker%"
  or name like "%ann parker%"
Improve this answer

Comments

0

If you're using MySQL (it isn't clear, so I'm just guessing), you should be able to do it like so:

SELECT * FROM table WHERE name RLIKE REPLACE(searchquery, ' ', '.*')

You'll have to deal with searchquery appropriately in whatever language you're querying from, of course. And it's slow, so very slow.

If you're using MySQL with MyISAM, creating a fulltext index on the column in question, and using MATCH() would be faster and probably more accurate:

SELECT * FROM table WHERE MATCH(name) AGAINST (searchquery)

It's faster than RLIKE, but it may not be quite what you're looking for, and if you're not using MyISAM, you can't use it anyway.

Improve this answer

3 Comments

This looks interesting. I only have the possibility to change the query itself nothing else. Is the same syntax also for MSSQL ? Otherwise I will take a look at the docu if you do not know it. Thanks!
@WorldSignia: At a glance neither look MSSQL compatible, unfortunately.
The latter certainly isn't MSSQL compatible, however there are MSSQL fulltext solutions. The former, I honestly have no idea, but I doubt it.
0 
SELECT * FROM table WHERE name LIKE 'Anne%' and name LIKE  '%Parker'
Improve this answer

Comments

0

Here's a brief solution:

SELECT *
FROM table
WHERE name like
    Left(@stringval,CharIndex(' ',@stringval))
    + '%'
    + Right(@stringval,CharIndex(' ',Reverse(@stringval)))

You'll have to forgive me if I'm a little off with the positions, but hopefully this'll cope with multiple middle names too.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.