How to select columns that have 2 values in it separated by a comma

Question

0.00/5 (No votes)

See more:

MySql and or SQL either one would help

How to select columns that have 2 values in it separated by a comma.

I was trying to use a WHERE LIKE but I knw that's completely wrong.

Example of values in my column named Subtype:

Passenger
SUV
Light Truck
Commercial
Passenger,Light Truck <------- looking to return columns like this only
Light Truck,Commercial <------- looking to return columns like this only

Thanks.

What I have tried:

What I tried is not even close to being correct

Posted 28-Jan-22 4:19am

Commish13

Updated 28-Jan-22 11:37am

Add a Solution

Comments

Richard Deeming 28-Jan-22 11:19am

That looks like a poor database design. Storing multiple values in a single column is an indication that your database isn't properly normalized.

But then your comment to solution 1 says that the comma isn't part of the value, which means your question needs to be updated with a clear and precise description of the problem. Include the structure of your table, some real sample data, and a description of what you are trying to do, what you have tried, and where you are stuck.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jsc42 · Answer 1 · 2022-01-28T04:45:00

Solution 1

WHERE LIKE sounds like a good plan! Try WHERE Subtype LIKE '%,%'

Posted 28-Jan-22 4:45am

jsc42

Comments

Commish13 28-Jan-22 11:05am

Thanks, but that does not work because the comma is not really part of the value or string

k5054 28-Jan-22 12:39pm

But, but, but ...
you asked "How to select 2 values separated by a comma"! You need to give a exact example of your data! jsc42 has given you the right answer to the question you asked. So now you need to reframe the question. What, exactly, does your column data look like?

Dave Kreskowiak · Answer 2 · 2022-01-28T11:37:00

The method in Solution 1 is the only real method you have to doing this.

Like Richard said in his comment, your database design is very flawed. YOu should not have multiple values like this in a single column. Your "subtypes" should have been their own table, then a mapping table between your main record and the subtypes should have been created, giving you the ability to have 0 or more subtypes for every main record, and easily queried.

Keep in mind that SQL does a terrible job of string manipulation. By giving SQL a query to split that string into multiple values, your database performance will suffer and not scale well. This is because instead of just returning a bunch of records, you're forcing SQL to run a function against every record returned. That kills performance!