Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Is it possible to write a sql query to find if a column contains part of the value in the string?

For example I have a column with some values
ID | Tech_skill
1  | Php, C++
2  | Css, Html
3  | Java, Php
4  | Html


If given a search string = 'Java,CSS,PHP,MySQL'
I want the row that contains Java or Css or PHP or MySQL to be displayed.
For example:
ID | Tech_skill
1  | Php, Css
2  | Css, Html
3  | Java, Php


What I have tried:

I've tried:
SELECT * FROM Skills
WHERE 'Java,CSS,PHP,MySQL' LIKE CONCAT('%',Tech_skill,'%');

AND
SELECT * FROM Skills
WHERE Tech_skill LIKE '%Java,CSS,PHP,MySQL%'


But the output is not what I want.
Posted
Updated 6-Nov-22 5:08am

Basically, you are storing your data wrong: you have massive amounts of duplication, potential spelling errors, and so forth.

Instead of storing your data as CSV in a column, use two additional tables:
KnownSkills:
ID      INT, IDENTITY, Primary Key
Desc    NVARCHAR

SkillsLink
That way, you can search for wanted skills, and JOIN back to the Users to get the info for want. You can also add and remove user skills very easily, prevent duplication, and also prevent "finger trouble" adding skills you can't find because they hit the wrong key while typing the list - "java.CSS, PHP" for example is really easy to mistype, and would mess you up properly.
 
Share this answer
 
Can it be done? Yeah, sure.

Is it the most efficient way to store that data and search it? H E L L N O! What you have is practically the worst way to store this data, making it very inefficient to search and using a method of search that makes the inefficiency problem even worse.

Database engines are designed to store data, search, and return related records efficiently, SO LONG AS you're not manipulating the data during the search. Database engines are NOT designed to efficiently manipulate strings, like searching for substrings in another strings.

You should be putting each skill in its own row, with its own ID. That makes it far easier to search.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900