MYSQL :: SELECT Where String Matches CONCAT Value?

Searching Longest String That Matches Best Query
I have a tricky search problem. I need to query for the longest matching string in a database which is SHORTER than or equal to my search string.

I have a database with names and cities:

fields: name, city
Meyer, London
Meyersholl, London
Meyeron, NY
Meyerball, Boston
Meyershill, Paris
Meyershilliman, Paris
Meyerballshill, Tokyo
Meyershillibollyman, Tokyo

What I am searching for is the best matching name (left to right) that is a substring of my searched name, with the longest match grouped by each city:

So lets say I am searching for the name
"MeyershillibollymanSushi"

then my query should return:

Meyer, London
Meyershill, Paris
Meyershillibollyman, Tokyo

So the looked up name field must be a substring of my search word and for each city I need to find the longest substring name that matches best my query word. Another constraint is that I need to do it all in one query.

In order to avoid to calculate each time the lenght of the name field I introduced an additional database field which holds the length of the name field as an integer - so my database looks now like this:

name: varchar(100)
city; varchar(100)
lenghtofname: int(11)

My solution so far

SELECT b1.city, b1.name
FROM myDB AS b1
JOIN (

SELECT city, MAX( lengthofname ) AS maxlength FROM `myDB`
WHERE name = left( 'MeyershillibollymanSushi', lengthofname )
GROUP BY city
) AS b2 ON b1.city = b2.city AND b1.name = left( 'MeyershillibollymanSushi', maxlength )

This query seems to be very expensive.

Is there a better way to do it?

Count The Matches Of A String Pattern Within A Text Field?
I would like to include a count of a user given string and return the results based on the highest number of matches within a text field. I am currently working with InnoDB. I found a way to do it with MyISAM but this technique will make things to complex for the db I am working with;

Create & Store A Concat String
I have two tables:

keywords(itemID, WordID) and
words(WordID,Word)

I would like to create a single string of the words associated with a single ItemID by concatenating words and store them into a new table wordstring(itemID, wordstring).

Select By Number Of Matches
I have a product / category mapping table and I would like to be able to Retrieve all products that match 3 of these categories. Could someone point me in the right direction?

LIKE:

SELECT * FROM Products WHERE catId IN ( 3, 4, 5 )

Obviously the above would return products that are in any category mentioned..

Number Of Matches Of A Select
I make a select with a select clause and I limit it to 5 results, but
I need to know if there are more matches to enable a "next" link to
see the others

I've thought about not limiting it in the query but displaying only 5
results so I could know if there are more matches I don't display, but
maybe it's slower.

My question is: Is there any functon that retrieves the number of
matches without loading data to make the system faster?

Big SELECT: Ordering Results By Where Matches Are Found
I'm sure there must be an accepted technique for this, but it's something I haven't tried before, so if anyone can point me in the right direction I'd be grateful.

I'm writing a search facility for a site where the data is stored in several tables - let's say 5 for this example - and I want to order my results according to where (if anywhere) matches are found. So...

Let's say I have tables 'speakers', 'topics', 'speakers_topics', 'articles', 'other'.
'speakers' is a table of speakers, with id, name and some text fields.
'topics' is a list of topics they address
'speakers_topics' relates the above two by pairs of id numbers
'articles' and 'other' are further tables of text data with possibly more than one row for some speakers, identified by id.

I want to search the data in the following order:
name from 'speakers'
topics
text data from 'speakers'
text data from 'articles' and 'other'
...and order the results according to where in that hierarchy a match is found.

So, if the user's search term matches one speaker's name field, another's topic and someone else's text data, that's the order in which the results should be ordered. Also, if the same person is matched from, say, both name and text fields (which is very likely, as their name will almost certainly appear in some of the text), the name should take precedence in the ordering.

To complicate matters further, I'd like if possible to extend this to an and/or situation. If the user enters two or more words, any results that match all the words should be ordered above those that match only some of the words.

I can probably do this relatively easily with a series of separate queries (I'm doing all this from PHP, by the way), but that strikes me as inefficient.

SELECT CONCAT() Subquery
SELECT * FROM
(SELECT CONCAT(comments.category,'s') FROM comments WHERE comments.author_user_id = '1')

The subquery alone yields "post" (`comments.category` is an ENUM() field); and I want to select all the rows from the "posts" database table. Ideally, the query would be processed like:

SELECT * FROM posts

How do I perform a string concatenation during a SELECT query?

SELECT Subquery CONCAT
SELECT * FROM
(SELECT CONCAT(comments.category,'s') FROM comments WHERE comments.author_user_id = '1')

The subquery alone yields "post" (`comments.category` is an ENUM() field); and I want to select all the rows from the "posts" database table. Ideally, the query would be processed like:

SELECT * FROM posts

How do I perform a string concatenation during a SELECT query with MySQL?

Can You Select And Concat Multiple Items Separated By "," ?
The select I have finds multiple values for a field. I want to take that field and create ONE field separated by commas.

Is that possible? I could do it in PHP but it will be easier programmatically if I can do it here.

For example:

HTML
Select field1 where x=2;
val1
val2
val3
val4
I want it to be like:

HTML
Select field1 where x=2;
val1, val2, val3, val4
where "val1, val2, val3, val4" is ONE field I can then use.

I am actually using this as a subquery so it's not as easy as php's explode/implode.

How To Select Rows Which Contains Certain String
How can I select rows which contains certain strings. Using "Like" clause we can do but its not giving exact results.

Select Match From A Stored Delimitated String?
I'm not even sure if I'm naming the question correctly. I hope I can
ask/explain it clearly enough to get a hint.

Essentially...

In A SELECT, Can I Replace Contents Of A Result String?
What I mean is this:

If I do

SELECT my_string FROM my_table LIMIT 1

and my_string is

"hi, this is a string",

how do I change all s:s to

"hi, thi# i# a #tring"?

Append String Of Column Values In SELECT Statement
I'm looking for syntax to append string of column values in SELECT statement.

For example, following is a SQL statement to select the property_address1, property_address2 and property_address3. The values should be returned in accordance to their respective columns.

SELECT property.property_address1, property.property_address2, property.property_address3
FROM orems_property property;

is there any syntax that will enable us to append property_address1, property_address2 and property_address3 is a single column? I've no idea of MySQL, but Oracle syntax would look like the following:

SELECT property.property_address1 || property.property_address2 || property.property_address3
FROM orems_property property;

Different Matches
need to perform different matches on a string. such as:
phonetic matches (maybe soundex())?
and proximity matches
synomymn matches
and override matches (ex: if i search for "10 commandments" it would then return "ten commandments" and/or all of the ten commandments)

Format Function In SQL String To Return From Numeric Field In Table To String
How would i form a format function in SQL string to return from numeric field in table to string:

1 --> '001'
5 --> '005'
57 --> '057'
125 --> '125'

Multiplying Matches
I need to think of a complex way of searching data in a table and also create a scoring system and display results considering their score. I need to keep some tokens and also need to compute the score with those values. The formula for computing the score might be different. (For example: Token1^4 + Token2 * 7 .....). I'll explain further what
those tokens mean and why do i need them. So it is useful l to know that score can be anything (useful for later query upgrades, adapted to customer's requirements). It is like a function - score(Token1, ... ,TokenN)

Suppose i have the following sql table: ...

Distinct Matches
I have a table with three Keys. riverID (Primary), river name, and state. I want to list all the states that have rivers in them. the following query returns states multiple times, becuase there are multiple rivers in each state.
SELECT `state`
FROM `rivers`What do I do to return the states that have rivers in them ony once?

If you don't understand my question, please ask for an example. If you do understand my question and know I'm not describing properly, please help me describe it.

Partial Matches
I'm trying to run a query that takes one field and looks for likeness in another and returns the matches. For example:

The field is mobile_no (mobile phone numbers no in 646-555-1212 format) and I'm tryna get matches against the field npa_nxx (NPA-NXX codes in 213-555 format)

Searching Matches Within A Field?
I'm using PHP to execute MySQL commands.

I have a MySQL table that consists of two fields:

id, product_title

each product has its own id and rows look like this:

1, "this is product widget number one"
2, "this is product widget number two"
3, "this is product widget number three"
4, "this is product widget number four"

I have HTTP_POST_DATA which look for matches within "product_title", so my query is:

CODESELECT * FROM table WHERE product_title LIKE '%$searchstring%'

Multiple Matches Per Regex
I am trying to create a mnemonic phone script, but I am having troubles with the SQL queries. Right now I use a MySQL regex query to find a word the first 3 numbers and then another MySQL regex query to match the last 4 numbers. For example:

DOG-COOL

However, the problem with this is that firstly, it is two separate queries, secondly it only matches in the set of 3 and 4, and thirdly it doesn't match words separated by non-words:

-------------------------------------
Regex: [GHI4][ABC2][MNO6][ABC2][MNO6][MNO6][JKL5]
-------------------------------------
I-AM-COOL
HA-6-COOL
HAM-COOL

Is there any way of doing this with MySQL?

Find Records Without Matches
I have two tables (A and B) with id fields in them, I would like a
list of ids that are in A but not in B. How would I do this in SQL?

Count() Rows With 0 Matches
I am using the code below to select all the 'centres' in the database that have 'sessions' today.

SELECT c.*, r.region, COUNT(*) AS count
FROM centres c, regions r, sessions st
WHERE c.c_id=st.c_id
AND st.session_date=CURDATE()
AND c.region_id=r.region_id
This works and the results might look like:

centre / count
centre1 / 20
centre2 / 10
centre3 / 3

However, is there a way to get MySQL to also select centres from the database that have no matches in the sessions table? So the results will look like this:

centre / count
centre1 / 20
centre2 / 10
centre3 / 3
centre4 / 0
centre5 / 0
centre6 / 0

Searching For Matches And Sorting The Results
I collect different data from users and store them in a database in the form of numbers. For example, here's a sample table:

country|sex|age|job|fat|married|
4 | 1 | 17 | 5 | 1 | 1 |
5 | 2 | 19 | 1 | 0 | 0 |
8 | 2 | 27 | 4 | 0 | 0 |
1 | 1 | 24 | 5 | 0 | 1 |

The numbers have special meanings to me (for example 5 in the country column refers to the USA...)

Now suppose I want to search my database for the 10 users who best match the following criteria:
live in USA, male, 25 years old, engineer, fat, not married
and in my "special number codes" here's an equivalent of the criteria above:
5,1,25,7,1,0

The question now is how can I compare the criteria (5,1,25,7,1,0) with all rows in my MySQL table and return the 10 rows that best matched that criteria (not necessarily full match, but best column-wise match) in descending order (best match -> least match)

Fulltext Search Not Returning Matches..
my query:

SELECT * FROM `links` WHERE MATCH ( KEYWORDS ) AGAINST ( 'paper' );

I have a table with 4 records in it of links to various newspapers, paper
and newspaper are both in the keywords field.

The keywords field has a fulltext index on it. Did I totally miss a step
somewhere? The query doesn't error, I just get 0 results back.

SQL Join - Ignore Value That Matches Certain Criteria
I have two tables, agreement and inventory. agreement table contains AgreementID, StartDate, EndDate, InventoryID. inventory table contains InventoryID and Description. I am using MySQL ver 3.23 so I can't do nested SELECT statements. Here's some sample data:

inventory
------------
1, Honda Civic
2, Mazda 626
3, Toyota Camry

agreement
---------------
1, 2004-11-02, 2004-11-30, 1
2, 2004-11-12, 2004-11-16, 2
3, 2004-12-05, 2004-12-07, 1

Problem: Given a date, getting a list of InventoryID that tells what cars are available for that date. I am not sure if this problem is solvable using a single query in MySQL ver 3.23. I came up with the following query, however, from above sample data, this query returns InventoryID's 1 and 3. It return's 1 because the query does not filter out record # 3 from the agreement table. Code:

SQL - Identifying Missing Matches In Two Tables
I'm trying to perform a query on some responses I have stored. I want to be
able to add up a score (using SUM() ) but first checking that all the
questions have been answered.

I've tried
SELECT *
FROM `questions`
LEFT JOIN `responses`
ON `questions`.`qid` = `responses`.`qid`
AND `responses`.`yearid` = 1 AND `responses`.`uid` = 1
WHERE `value` = NULL OR '-1'
ORDER BY value DESC

but it only returns all the rows even if value is '1' those question

a entry for a question is created with a `value` value of -1 when it is
accessed.

In short, I need to be able to identify those question which either don't
have an entry in `responses` for them or have an entry set to `-1`

RESPONSES
+-------+---------+
| Field | Type |
+-------+---------+
| uid | int(11) |
| yearid | int(11) |
| qid | int(11) |
| value | int(1) |
+-------+---------+

QUESTIONS
+----------+--------------+
| Field | Type |
+----------+--------------+
| qid | int(11) |
| qtext | varchar(100) |
+----------+--------------+

Identifying Missing Matches In Two Tables
I'm trying to perform a query on some responses I have stored. I want to be able to add up a score (using SUM() ) but first checking that all the questions have been answered.

I've tried........

Matching/Ordering For Full And Partial Matches
I have an autocomplete field that will be used to search for individual items (lets call them recipes.) Each recipe will be assigned keywords and users will enter keywords in the autocomplete and be presented with results based on the following logic:

If the user enters, say, 3 words (keywords), the first results should be recipes that have keywords matching all 3 entered keywords, the next result should be recipes whose associated keywords match 2 of the entered words and 1 partial match, the next result should be recipes with keywords having 1 complete match and 2 partial matches, etc.

For instance, for these rows/keywords:
row1 | baked cheddar chicken
row2 | baked honey mustard chicken breast
row 3| boneless chicken breast bake

if the user entered the text "bake chicken breast hon" the rows should be returned in the order (note, each space should be considered an implicit AND condition):row 3 (3 full matches)row2 (2 full matches -- "chicken" and "breast", 2 partial matches -- "baked" and "honey")row 1 (1 match "chicken" and 1 partial match "baked"Any ideas how I could do that in SQL? I have tried experimenting with FULLTEXT Match/Against but it does not allow me to rank them as described above.

Finding Field In Table That Matches User Supplied Input
I've come across match/against, field, and locate. I've been unable to use these to match against columns.

As an example, I have a table with two columns, an index number and a domain name. The user supplies a FQDN (fully qualified domain name). I would like to compare all of the domainname rows to see if they match against the FQDN and return a go/no-go value.

The match function is close, but works in reverse. I cannot see a way to get locate or field to work with fields.

Concat Maybe?
Theres a way in mysql to get a comma separated list of fields in a one to many relationship - rather than multiple rows for each record in the 'one' table. But I can't remember what it is, I thought it was concat but that didn't work... I've done it before but I can't remember what it was or why.

Using CONCAT
Is it possible to make query which would select some value even column would be NULL/EMPTY ?

Example:

Table
name

John
NULL
Mike

SELECT CONCAT_WS('something',name) FROM table;

results:
Johh
Something
Mike

WHERE With CONCAT
I have a table A with a (varchar 20) A.field and a table B with two (varchar 10) fields: B.field1 and B.field2.

How can I make a SELECT with a WHERE of this type:

WHERE A.field = CONCAT (B.field1 , B.field2)

I get an error in CONCAT. How is the string concatenation in mySQL?

Concat
I'm trying to combine 2 fields into 1 new field using concat. This is what I using

UPDATE 'idxactrs' SET 'idxactrs.desc' = concat(remarks1,remarks2)

It just says "You have an error in your SQL syntax near ''idxactrs' ...

Any Ideas?

Using Concat()
I'm trying to combine 2 fields into 1 new field using concat. This is what I using

UPDATE 'idxactrs' SET 'idxactrs.desc' = concat(remarks1,remarks2)

It just says "You have an error in your SQL syntax near ''idxactrs' ...

Use CONCAT
I'm having lots of problems using the CONCAT instruction. I'm trying to store in a variable (@f) a COUNT, but the system always crashes and I don't know why. Here you have the lines:

set f=0;
set @f:=f;
set @stmt3:=CONCAT("select count(*) into `",@f,"` from `",@n,"` where sourceurl=`",@b,"` and link=`",@c,"`");
prepare query from @stmt3;
execute query;

Concat
I'm having problem using concat

select CONCAT(firstname,' ', lastname) as fullname from tbltable where fullname = 'name';

It say Unknown column 'fullname' in 'where clause'. It is my sql wrong or what?

How To Use CONCAT In The 'FROM' Section
For example I'd like to use the following query:
SELECT * FROM CONCAT('table_', 'name');

Is there a way to do this? or something similar?

How Does Mysql_field_len Act On CONCAT?
I am quite puzzled by this. I have two fields which if selected by themselves will return a field length of 2 using mysql_field_len. However if I select them as CONCAT(field1,' ',field2), the field length is much greater than the 5 I would expect. It looks to be about 20. Is there a way to control this length? Where is it getting the field length from?

EDIT: Nevermind, this can be done using CAST(CONCAT(field1,' ',field2) AS CHAR(5))

Concat Two Tables
I have two tables, one containing payments to a supplier and one containing
invoices issued by a supplier. I'd like to get a total from each table with
one query.

I need somehow to list all payments and all transactions together in one
table result set.

i.e.....

Using CONCAT In Subqueries
SELECT tblmonths.fldMonth, tblyears.fldYear, tblmonths.fldID, CONCAT(tblmonths.fldID," ", tblyears.fldYear)
FROM tblmonths, tblyears
where CONCAT(tblmonths.fldID," ", tblyears.fldYear)
NOT EXISTS
(SELECT CONCAT(fldMonth, " ", fldYear)
from tblexpensesclaims)
GROUP BY tblyears.fldYear, tblmonths.fldID

CONCAT And Variables
I have the following statement:

------------------
SELECT CONCAT('hxxp://www.whatever.com/all.html?t=4&p=1&u=',@c) into @c;

set @stmt3:=CONCAT("INSERT INTO webs values ('",@c,"')");
prepare query from @stmt3;
execute query;
--------------

I think that I'm using correctly the CONCAT function but it doesn't work.

CONCAT Function (bug?)
I'm needed to insert large BLOBs into a database. With the 1MB packet
limit, sending larger amounts of data would be difficult, so I had a neat
idea. I would do an initial insert of an empty record and get the
auto_insert ID from the response, and then loop through, appending data to
the record.

My table is simple. One unsigned int auto_increment field (DataID), and
one long blob field (BinaryData).

When I loop through the data to send, I run:

UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data
here') WHERE DataID = 35

The binary data I insert I escape null characters, backslashes, single and
double quotes. The data seems to insert fine.

The problem is that as I increase the amount of data in the field, CONCAT
seems to drop all but the last 416k of the data. Thus if I loop through
adding 400k blocks at a time (Which I do) I am left with at most 800k of
data in the blob field.

Using 4k blocks I end up with 419k of data in the field when all is said
and done.

Update Using Concat
I have a form that I call for the data from a database table. Once the table is filled with data, the viewer has the option to add data to one field in the form. I want to be able to add the new comments without losing the old comments in the db column.

Field A has the existing data ($Remarks)
Field B has the new comments ($NRemarks)

I have tried various CONCAT statements but none of them work, and I am thinking that this may be because Field B is not a column in the DB, as it is new information being added.

Is there a way to keep the data in the DB column called Remarks and add the information in Field B to it on a seperate line.

I don't know if there is a command like append or edit or add to, that will add information to the existing information. I have tried the following:

$sql="UPDATE 'workorder' SET Remarks CONCAT (Remarks, NRemarks)";
?>

$sql="UPDATE 'workorder' SET Remarks CONCAT ($Remarks, $NRemarks)FROM workorder WHERE Work = '$Contact'";
?>

$sql="UPDATE `workorder` SET Remarks = CONCAT(SELECT Remarks FROM workorder
WHERE Work = '$Contact', '', '$NRemarks') WHERE Work = '$Contact'";
?>

Using CONCAT For 2 Tables
Currently I'm using this SQL query to connect 2 tables but now, I need to add a 3rd table in the same manner as the "links" table in the following query.

SELECT * FROM tracker LEFT JOIN links ON tracker.location = CONCAT('link[', links.linkid, ']') ORDER BY tracker.occurred DESC LIMIT 0, 15

Problem With Concat
I have the following select:

SELECT distinct name, main.ACCOUNT,
concat(FlatNo,' , ',FlatName,' , ',number,' , ',`St Name`,' , ',SuburbName,' , ',`Town Name`,' , ',Stand) AS Address
FROM ...

I need to return all the available address information that we have but the select returns only about 15 addresses in a DB of about 100000. Some of the address fields do contain null values and the SELECT CONCAT is returning null for the whole row if any field contains a null value.

Is there anyway round this? I have tried IFNULL(Field,"") inside the concat but this does not seem to work either.

CONCAT Function
I'm needed to insert large BLOBs into a database. With the 1MB packet
limit, sending larger amounts of data would be difficult, so I had a neat
idea. I would do an initial insert of an empty record and get the
auto_insert ID from the response, and then loop through, appending data to
the record.

My table is simple. One unsigned int auto_increment field (DataID), and
one long blob field (BinaryData).

When I loop through the data to send, I run:

UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data
here') WHERE DataID = 35

The binary data I insert I escape null characters, backslashes, single and
double quotes. The data seems to insert fine.

The problem is that as I increase the amount of data in the field, CONCAT
seems to drop all but the last 416k of the data. Thus if I loop through
adding 400k blocks at a time (Which I do) I am left with at most 800k of
data in the blob field.

Using 4k blocks I end up with 419k of data in the field when all is said
and done.

Please let me know if/when this will be fixed, and if there is a
work around that might be used, r a better way to insert BLOB data is
known.

CONCAT All Columns
I want to produce a query that does something like this:

SELECT MD5(CONCAT(*)) FROM table WHERE 1

* is all the columns.
I don't want to have to type in every column name, but have MySQL do it.

Problems With Concat
I'm having problems with Concat. I have this piece of code:

set @m:=a;
SET @stmt1:=CONCAT("INSERT INTO ",@m," VALUES (b,c)");
PREPARE query from @stmt1;
execute query;

b and c are cursors and when it tries to execute the query and I get the following error:

"Unknown column 'b' in field list"

It seems that is not doing correctly the CONCAT?

Concat For Numbers
I am interested in performing a concat for numbers with a space
e.g.
select concat (num1, ' ', num2) from tablex as numconc

such that if num1 is 123 and num2 is 456 the result would be '123 456'
I believe concat should work for a string rather than number format, however having created the fields num1 and num2 in string format it still does not work.
Any advice on why this isn't working, or an alternative method to achieve the result would be most welcome.