CODEWITHSUNDEEP
sqlsql-server
user459611
user459611

Reputation:

Replace in SQL Query

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

Upvotes: 1

Views: 626

Answers (6)

THEn
THEn

Reputation: 1938

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);

Upvotes: 0

Stu Pegg
Stu Pegg

Reputation: 1317

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.

Upvotes: 0

Morons
Morons

Reputation: 199

SELECT * FROM table WHERE name LIKE 'Anne%' and name LIKE  '%Parker'

Upvotes: 0

Arantor
Arantor

Reputation: 597

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.

Upvotes: 0

nathan gonzalez
nathan gonzalez

Reputation: 11987

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

Upvotes: 1

inetknght
inetknght

Reputation: 4439

select
  *
from
  table
where
  name like "%anne marie parker%"
  or name like "%ann parker%"

Upvotes: 0

Related Questions