CODEWITHSUNDEEP
sqldatabasederby
user2318813
user2318813

Reputation: 64

having trouble with writing a proper sql query

I have a table datatable with following data

No  Data
1   access author basic chapter data database information
2   author basic black data chapter creating overview project scope using code 
3   basic data edition author special any assumed been book contained corporation damages

I am trying to write a query which does pattern matching with the help of LIKE clause this is my query

rs=s.executeQuery("SELECT No from datatable where Data LIKE '%author basic data%');

which is supposed to retrieve all the records that is No 1, 2 and 3 but it returns nothing however

if i execute this query

rs=s.executeQuery("SELECT No from datatable where Data LIKE '%author basic%');

which gives output as No 1 and 2 but not No 3 so how can i write such a query which gives me the desirable output

thanks in advance

Upvotes: 1

Views: 63

Answers (2)

Dyre
Dyre

Reputation: 547

Looks like you have something like

CREATE TABLE datatable('No' INT, 'Data' VARCHAR(...))

by grouping a lot of attributes together in a single column (VARCHAR, CLOB, BLOB...) you cannot harness the power of SQL.

If you rather do something like 

CREATE TABLE <meaningful name>('No' INT, AUTHOR VARCHAR(...), TITLE VARCHAR, ...)

you avoid using LIKE for all your queries and you can, as a commenter said, create indices on the columns most often used to access the data to speed up access.

Other benefits include using aggregating functions on numerical data and joining with other tables.

Upvotes: 1

potashin
potashin

Reputation: 44581

You query checks if there is a full substring like %author basic data%. Try this :

SELECT 
   No 
FROM 
   datatable 
WHERE 
   Data LIKE '%author%' AND 
   Data LIKE '%basic%' AND 
   Data LIKE '%data%'

Upvotes: 3

Related Questions