1,105,423 Community Members

Select a record where value contains substring

Member Avatar
nigelburrell
Light Poster
38 posts since Jun 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi,

I trying select records where a field contains only one character of a string of characters.

i.e.

SELECT * FROM users WHERE user_role [contains any one character from...] 'ASD'

so in this case, the query would return all records where the value of 'user_role' field is either 'A' or 'S' or 'D'.

Any ideas how can I achieve this?

Member Avatar
ShawnCplus
Code Monkey
1,564 posts since Apr 2005
Reputation Points: 456 [?]
Q&As Helped to Solve: 269 [?]
Skill Endorsements: 39 [?]
Team Colleague
 
0
 

You might be able to do

WHERE user_role IN ('A', 'S', 'D')
Member Avatar
nigelburrell
Light Poster
38 posts since Jun 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

You might be able to do

WHERE user_role IN ('A', 'S', 'D')

Thanks for your suggestion.

Yes, I did pursue that option at the beginning, but I want the SQL query to live inside a function and simply pass in a string of sometimes one, two, or even more characters, without having to rework the string for the SQL query.

Thinking more about it, perhaps the best option is to exactly that... inspect the incoming string and create an IN clause string, i.e. 'A', 'B', 'C' etc...

Member Avatar
ShawnCplus
Code Monkey
1,564 posts since Apr 2005
Reputation Points: 456 [?]
Q&As Helped to Solve: 269 [?]
Skill Endorsements: 39 [?]
Team Colleague
 
0
 

Give me a real-world example of what you want because you're sort of all over the place on how you want the query to work. You really only have a few options

-- LIKE matches some_inner_string_here
WHERE string LIKE '%inner_string%'
-- LIKE matches whole_string
WHERE string LIKE 'whole_string'
-- WHERE IN matches a group of things
WHERE character IN ('A', 'B', 'C', 'D')
Member Avatar
nigelburrell
Light Poster
38 posts since Jun 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Give me a real-world example of what you want because you're sort of all over the place on how you want the query to work. You really only have a few options

-- LIKE matches some_inner_string_here
WHERE string LIKE '%inner_string%'
-- LIKE matches whole_string
WHERE string LIKE 'whole_string'
-- WHERE IN matches a group of things
WHERE character IN ('A', 'B', 'C', 'D')

That's perfect... what you listed are the options I'm looking for. Thanks for your help.

Member Avatar
ripraw
Newbie Poster
5 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

i've got the same problem but i need to select records where a field contains a whole word. i cant get any of the above to work

a real life example:

- a user has added an event to my website.
- in the artist field they have typed 'dj1, band2'

i need to make a list of all the events 'dj1' has been booked for

can any1 help?
i no its probably wrong but i've only used 1 table because i've forgotten everything i learnt about relating multiple tables

Member Avatar
drjohn
Posting Pro
526 posts since Mar 2010
Reputation Points: 50 [?]
Q&As Helped to Solve: 106 [?]
Skill Endorsements: 4 [?]
 
0
 

shouldn't let them store two items in one field!!!
one field for things like dj1, another of things like band2
databases with two or more things in the same field are not normalised.
you may need a link table to hold a series on event, band links, the design of which depends on the rest of your tables.

Member Avatar
ripraw
Newbie Poster
5 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

yh i had a feeling i was going to need a relational database. i've made one in MS Access to emulate what i need MySql to do

maybe you can help me still. i'm new to all this and i can't figure out how to create relationship between tables in MySql. can it even be done? how do i create a foreign key - primary key link? i've looked all over the internet.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article