Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Query gives #1305 - FUNCTION database-name.LEN does not exist; WHY?

Tags:

mysql

EDIT3 MySQL Fiddle Here. I have made the example MySQL so you can see the actual problems. While I am expecting to have Jamie Foxx, Christoph Waltz in the 2 names result it gives much more. Even though it is written the exact same way as it was in the SQL example where it return the names correctly. :/

EDIT2 SQL Fiddle here. This is a much simpler version, but the logic is there. I need to have this working in MySQL as the fiddle is in SQL. When I just replace the SQL functions with LENGTH and LOCATE and test it with PhpMyAdmin it returns the entire content of the actors column, not just the first two names. I am even more confused now as the LOCATE is supposed to be equivalent to the CHARINDEX.

EDIT1 *Oh, I just found it that neither LEN or CHARINDEX exist in MySQL. I think I can replace LEN with LENGTH, but I don't know what to do with the CHARINDEX I tried using LOCATE but the result is incorrect it gives the full content of the actors field. Any insight on this?

Another follow up on my previous questions. This should be the end of it though. I had part of the query that uses the len function working on SQL Fiddle, but as soon as I implemented it into the final query in my actual database I getting the function does not exist error. Listing all I consider related 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)

The error

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

Setup

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, as the statement worked fine on SQL Fiddle. If you'd need any additional information just ask for it. Thank you all for reading and in advance for your replies.

BTW: Any chance it is caused by actors varchar(10000)?

like image 600
Cream Whipped Airplane Avatar asked Jan 04 '14 13:01

Cream Whipped Airplane


People also ask

What is an example of a SQL query?

These are perhaps the most useful SQL queries examples. In the example below, we are extracting the “Student_ID” column or attribute from the table “STUDENT”. The select statement is used to select data from the database. If you want to display all the attributes from a particular table, this is the right query to use: 3.

What makes a great query letter?

A great query letter — one that gets an agent to request your manuscript — is one that both checks all the boxes and is unique to you, your book, and the agent in question.

Why do I need to query my agent?

The query also makes clear to the agent what materials the author has available (here, the complete manuscript), something which is especially important when querying with a nonfiction title, where you could be submitting either a book proposal or a full manuscript. This helps the agent know where you’re at in your writing journey straight away.

What are the important SQL queries for coding&optimization?

Here is a list of SQL queries that are really important for coding & optimization. Each of the queries in SQL tutorial is consequential to almost every system that interacts with an SQL database. 1. Retrieving Tables 31. Database Management 2. Selecting Columns from a Table 32. Adding Tables to Our New DB 3.


1 Answers

MySQL doesn't have a built-in CHARINDEX() function.Instead you can use LOCATE equivalent to 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)

See fiddle demo

If you just want to show two actors name 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 fiddle demo

like image 197
M Khalid Junaid Avatar answered Nov 15 '22 16:11

M Khalid Junaid