Geek Answers Handbook

The query gives # 1305 - the database name FUNCTION.LEN does not exist; WHAT FOR?

EDIT3 MySQL Fiddle Here . I gave a MySQL example so you can see the actual problems. Waiting for Jamie Foxx, Christoph Waltz result of 2 names I get much more. Even if it is written exactly the same as in the SQL example, where it correctly returns the names.: /

EDIT2 SQL Fiddle here . This is a much simpler version, but there is logic. I need this to work in MySQL since the script is in SQL . When I simply replace the SQL functions with LENGTH and LOCATE and test it with PhpMyAdmin, it returns the entire contents of the actors column, not just the first two names. I'm even more confused, since LOCATE should be equivalent to CHARINDEX .

EDIT1 * Oh, I just found that neither LEN nor CHARINDEX exists in MySQL . I think I can replace LEN with LENGTH , but I do not know what to do with CHARINDEX , I tried to use LOCATE , but the result is incorrect, it gives the full contents of the actors field. Any understanding of this?

Another continuation of my previous questions. But that should be the end. I had a part of a query that uses a LEN function that runs on SQL Fiddle, but as soon as I injected it into the final query in my real database, I get this function, there is no error. List of all reviewed below:

MySQL query

 SELECT title, director, thumb, LEFT(actors, LEN(actors) - CHARINDEX(', ', actors))AS '2 names' FROM test WHERE MATCH (title, director, actors) AGAINST ('arc*' IN BOOLEAN MODE)

Error

 #1305 - FUNCTION database-name.LEN does not exist

Customization

 OS: MAC OSX SERVER: MAMP DB ACCESS: PhpMyAdmin

SHOW CREATE TABLE test

 CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `director` varchar(255) NOT NULL, `actors` varchar(10000) NOT NULL DEFAULT 'Jamie Foxx, Christoph Waltz, Leonardo DiCaprio, Kerry Washington, Samuel L. Jackson', `summary` text NOT NULL, `cover` varchar(255) NOT NULL DEFAULT 'http://localhost:8888/assets/OBS/img/uploads/covers-thumb/django_thumb.jpg', `thumb` varchar(255) NOT NULL DEFAULT 'http://localhost:8888/assets/OBS/img/uploads/covers-bug/django1_cover.jpg', `trailer` varchar(255) NOT NULL DEFAULT 'fnaojlfdUbs', PRIMARY KEY (`id`), FULLTEXT KEY `myindex` (`title`,`director`,`actors`) ) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=utf8

I really don’t understand why I am getting this error, because the statement works fine on SQL Fiddle. If you need more information, just ask about it. Thanks to everyone for reading and in advance for your answers.

By the way: is any chance caused by actors varchar(10000) ?

+6
source share
1 answer

MySQL does not have a built-in CHARINDEX () function. Instead, you can use the LOCATE equivalent of charindex, and instead of LEN, you can use LENGTH

 SELECT title, director, thumb, LEFT(actors, LENGTH(actors) - LOCATE(', ', actors))AS '2 names' FROM test WHERE MATCH (title, director, actors) AGAINST ('arc*' IN BOOLEAN MODE)

Watch a demo of scripts

If you just want to show the name of two actors, you can use SUBSTRING_INDEX

 SELECT title, director, thumb, SUBSTRING_INDEX(actors, ',', 2) AS '2 names' FROM test WHERE MATCH (title, director, actors) AGAINST ('test*' IN BOOLEAN MODE)

See second demo of scripts

+8
source

More articles:


All Articles