How to filter data from from sql table?

All we need is an easy explanation of the problem, so here it is.

I have a table like this.

epfno designation
10 HELPER – BELT
11 CANTEEN HELPER
12 ABC HELPER XYZ NEW
13 XYZ HELPER NEW

Now I need to filter all the helpers by using ‘HELPER’ word. How can I get my data?

Thank you

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You may use LIKE operator to get your expected result:

SELECT epfno, designation
FROM TableName
WHERE designation LIKE '%HELPER%'

if you are need the results ending with HELPER then try LIKE '%HELPER' and
if you are need the results starting with HELPER then try LIKE 'HELPER%'

Method 2

You need to rethink your table structure.
What you have here breaks the most basic of Data Normalisation Rules, namely that you should only have one value in any one field. Here, you have multiple values (e.g. ‘ABC’, ‘HELPER’, ‘XYZ’ & ‘NEW’) in one field and that’s a problem.

Any search you do on this field will likely use a Table Scan, because it has to read each and every row in order to find the value you want "buried" somewhere inside the field’s value.

You need to break out these values into a new table, with one row per value:

+-------+-------------+
| epfno | designation | 
+-------+-------------+
|    10 | HELPER      | 
|    10 | BELT        |
|    11 | CANTEEN     |
|    11 | HELPER      | 
|    12 | ABC         | 
|    12 | HELPER      | 
|    12 | XYZ         | 
|    12 | NEW         | 
|    13 | XYZ         | 
|    13 | HELPER      |
|    13 | NEW         |
+-------+-------------+

Armed with this table (and some suitable indexes), finding those who are "HELPER"s becomes simplicity itself (and far, far more efficient for the database to process):

select efpno 
from TableName 
where designation = 'HELPER' 
order by 1 ; 

+-------+
| epfno |
+-------+
|    10 |
|    11 |
|    12 |
|    13 |
+-------+

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply