MySQL return results for letter at start of each word

[AnotherMuggle]

I have a field in a mySQL database that contains a sentence.

I want to be able to run a search that looks at the first letter of each word in the field. So if I search for "A" for example, it will return results where any of the words in the field start with "A".

So, for example, this would return a result:

"A dog sat on the mat".

And so would this:

"There was A dog sat on the mat".

Is this possible?

Thanks,
TK

[chazzy]

what if you did this...


select columns
from table
where this_column like 'A%' or this_column like '% A%';


Does that get them?

[AnotherMuggle]

what if you did this...


select columns
from table
where this_column like 'A%' or this_column like '% A%';


Does that get them?

Unfortunately that returns results such as:

"Dog sat on the mat"

because of the a's in sat and mat. I only want it to search for the letters at the beginning of words.

[yamaharuss]

Then remove the second part of the query:

select columns
from table
where this_column like 'A%';

[AnotherMuggle]

Then remove the second part of the query:

select columns
from table
where this_column like 'A%';

That is my problem.

That query will only search for the first letter of the first word.

Thanks none the less :)

[chazzy]

Unfortunately that returns results such as:

"Dog sat on the mat"

because of the a's in sat and mat. I only want it to search for the letters at the beginning of words.

Did you even try running my query? note that it's % (space) A%. This is the same I ran on my system, works fine.


mysql> insert into discussion_topic(body,title) values('this is some text that i am adding','this is the title.');
Query OK, 1 row affected (0.06 sec)

mysql> select * from discussion_topic where body like '% a%';
+----+--------+------------------------------------+--------------------+--------------------------+----------+---------
-----------+------------+-----------+-----------+
| id | target | body | commenting_enabled | guest_commenting_enabled | activeOn | title
| targetType | createdOn | poster_id |
+----+--------+------------------------------------+--------------------+--------------------------+----------+---------
-----------+------------+-----------+-----------+
| 1 | NULL | this is some text that i am adding | NULL | NULL | NULL | this is
the title. | NULL | NULL | NULL |
+----+--------+------------------------------------+--------------------+--------------------------+----------+---------
-----------+------------+-----------+-----------+
1 row in set (0.01 sec)

mysql> select * from discussion_topic where body like '% h%';
Empty set (0.00 sec)


As you can see, it returns the row when i use a because there are words that start with a. it doesn't return the row when i use h because no words start with h.

[AnotherMuggle]

Did you even try running my query? note that it's % (space) A%. This is the same I ran on my system, works fine.


mysql> insert into discussion_topic(body,title) values('this is some text that i am adding','this is the title.');
Query OK, 1 row affected (0.06 sec)

mysql> select * from discussion_topic where body like '% a%';
+----+--------+------------------------------------+--------------------+--------------------------+----------+---------
-----------+------------+-----------+-----------+
| id | target | body | commenting_enabled | guest_commenting_enabled | activeOn | title
| targetType | createdOn | poster_id |
+----+--------+------------------------------------+--------------------+--------------------------+----------+---------
-----------+------------+-----------+-----------+
| 1 | NULL | this is some text that i am adding | NULL | NULL | NULL | this is
the title. | NULL | NULL | NULL |
+----+--------+------------------------------------+--------------------+--------------------------+----------+---------
-----------+------------+-----------+-----------+
1 row in set (0.01 sec)

mysql> select * from discussion_topic where body like '% h%';
Empty set (0.00 sec)


As you can see, it returns the row when i use a because there are words that start with a. it doesn't return the row when i use h because no words start with h.

Yes I tried to run the query and it didn't appear to work. However I think I may have omitted the space. I will have a shot tomorrow and get back to you.

Thanks,
TK

[AnotherMuggle]

It still seems to be returning results with an A mid sentence:
http://img292.imageshack.us/img292/5669/55905903xv0.th.jpg (http://img292.imageshack.us/my.php?image=55905903xv0.jpg)

Is the query being run correctly?

Thanks

[chazzy]

It still seems to be returning results with an A mid sentence:
http://img292.imageshack.us/img292/5669/55905903xv0.th.jpg (http://img292.imageshack.us/my.php?image=55905903xv0.jpg)

Is the query being run correctly?

Thanks

Does 'and' start with the letter a? :)

[AnotherMuggle]

Does 'and' start with the letter a? :)


No :o ...OK, so it does lol.

In that case, it does work.

Thanks a lot for you time and patience ;)

[chazzy]

if you want it to skip instances of ' and ' you can change your where clause to this


WHERE
replace(mealName,' and ',' ') like 'A%' or replace(mealName,' and ',' ') like '% A%'

[glexgomez]

why not try using the follow command
select fields
from table
where left( field, 1 ) = 'A' or fields like '% A'