Substring within MATCH WHERE clause

[KingSlick]

Hi, I have a search query that is working well. The query is using "MATCH (fields) AGAINST ($keywords IN BOOLEAN MODE)" in the WHERE clause. I need to have the search query only search about half of the field. I was thinking I could use SUBSTR within the MATCH portion of the field. As noted below...

 SELECT s.articleID
FROM search_contents s
WHERE MATCH (
s.title, SUBSTR( s.abstract, 0, POSITION( "Keywords: ", s.abstract ) )
)
AGAINST (
'+breast +cancer'
IN BOOLEAN
MODE
)
LIMIT 0 , 20

However, I keep getting the following error...

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUBSTR( s.abstract, 0, POSITION(
"Keywords: ", s.abstract
) )
)
AGAINST (
'' at line 4

Any suggestions in getting this accomplished?

Thanks in advance
-SM

 

[tsuji]

>SUBSTR( s.abstract, 0, POSITION( "Keywords: ", s.abstract ) )
[tt]SUBSTR( s.abstract, [red]1[/red], POSITION( "Keywords: " [red]IN[/red] s.abstract ) )[/tt]

[1] position argument is 1-based (positive from the start of the string, negative from the end of the string). If you really mean 0 the second parameter, the return is always empty string.
[2] Position() use IN keyword. If you want to spare In, and instead use comman separator, the function is called LOCATE(substr,str).

 

[KingSlick]

Well I am getting the same error by making those changes. Is it possible to even include a substr within the match portion of the query?

-SM

 

[tsuji]

[3] As to the substr() part, I have no doubt yours is erroneous and the change is a must.

[4] As to using the substr(), meaning only part of the data in a column, that is another matter and the answer is probably you can't. Reason? That effective substr() is not fulltext indexed.

[4.1] If you must, one solution is to create a temporary table and place the substr() into the column of "article" in the temporary table. Alter the temp table by adding the fulltext indices corresponding to title and abstract. A quick sketch is this.
[tt]
[blue]-- let search_contents be the original table you're working on.
drop table if exists Y;
create temporary table Y like search_contents;
-- suppose there is only three columns that you've exhibited (the rest, there is no way the forum can know) being sucessively articleID, title and abstract.
insert into Y
select s.articleID, s.title, SUBSTR(s.abstract, 1, POSITION("Keywords: " IN s.abstract)) from search_contents as s;
alter table Y add fulltext(title,abstract);[/blue]
SELECT s.articleID
FROM [red]Y[/red] s
WHERE MATCH (
s.title, [red]s.abstract[/red]
)
AGAINST (
'+breast +cancer'
IN BOOLEAN
MODE
)
LIMIT 0 , 20
[/tt]

 

[tsuji]

>[self]...and place the substr() into the column of "article"
should be read
[tt]...and place the substr() into the column of "abstract"[/tt]