Calling strings in records.

Forum Moderators: open

Message Too Old, No Replies

Calling strings in records.

jonny0000

8:20 am on Jul 9, 2010 (gmt 0)

I am trying to call all records in selected field that contain denoted strings.

This far in the SELECT statment I have:

WHERE field_name in('string', 'string2', 'string3')

This only seems to call records that match string, string2 and string3 exactly.

I tried the following:

WHERE field_name in('string%', 'string2%', 'string3%')
&
WHERE field_name in('%string%', '%string2%', '%string3%')

to wildcard the strings to return records where strings are containing within them. This didnt work.

Any ideas?

Using MySql DB.

rocknbil

5:46 pm on Jul 9, 2010 (gmt 0)

where (field_name like '%string%') or (field_name like '%string2%') or (field_name like '%string3%')

Some notes:

If used with an and, make sure the "or's" are bound to the "and" or it may return true only on the first "and" or any of the "or's" following. Correct:

where some_other_field='some condition' and ((field_name like '%string%') or (field_name like '%string2%') or (field_name like '%string3%'))

likely incorrect:

where some_other_field='some condition and (field_name like '%string%') or (field_name like '%string2%') or (field_name like '%string3%')

The wild card will match "anything" on either side of the term, if the term is "widget" it will match on

good widgets
widgetizer
techno-widget

You may want to add a "starts with, anywhere, ends with, exact match" selection from the front end to result in any of the three below:

radio = starts with

where (field_name like 'string%') or (field_name like 'string2%') or (field_name like 'string3%')

radio = ends with

where (field_name like '%string') or (field_name like '%string2') or (field_name like '%string3')

radio = anywhere

where (field_name like '%string%') or (field_name like '%string2%') or (field_name like '%string3%')

radio = exact match

where (field_name = 'string') or (field_name = 'string2') or (field_name = 'string3')

topr8

5:58 pm on Jul 9, 2010 (gmt 0)

alternatively, create a full text index on the field_name

then:

SELECT * FROM table WHERE MATCH(name_of_full_text_index) AGAINST('string', 'string2', 'string3')