Searching for a string in a column

Question

I need to show the results from this column where Product_name column contains 'Documentation' or 'documentation' in a result. The query must return a result regardless of whether the word is in lowercase or uppercase

https://i.stack.imgur.com/bjLuY.png

SELECT UPPER(PROD_NAME)as PROD_NAME, LENGTH(PROD_NAME) as PROD_NAME_LEN
FROM PRODUCTS 
WHERE (PROD_NAME like '%Documentation%'
 or PROD_NAME like '%DOCUMETATION%')
 and LENGTH(PROD_NAME) <= 35
    order by 2 DESC;

I found this solution, any suggestions

Does this answer your question? [Oracle DB: How can I write query ignoring case?](https://stackoverflow.com/questions/1031844/oracle-db-how-can-i-write-query-ignoring-case) — Jonas Metzler, Nov 05 '22 at 14:10
In case you are surprised the query didn't return any uppercase matches: there is an N missign in '%DOCUMETATION%'. — Thorsten Kettner, Nov 06 '22 at 12:05
And just a general remark that has nothing to do with your question: I see that you are trying to get your query reaable by using upper and lower case. But there is no consistency. Some key words are in lower case (`as`, `like`, `or`, ...), some in upper case (`SELECT`, `UPPER`, `FROM`). Be consistent, e.g. have all keywords in upper case and all names in lower case (`SELECT UPPER(prod_name) AS prod_name, LENGTH(prod_name) AS prod_name_len ...`). — Thorsten Kettner, Nov 06 '22 at 12:09

score 2 · Accepted Answer · answered Nov 05 '22 at 14:05

2

SELECT UPPER(PROD_NAME)as PROD_NAME, LENGTH(PROD_NAME) as PROD_NAME_LEN
FROM PRODUCTS 
WHERE lower(PROD_NAME) like '%documentation%'
 and LENGTH(PROD_NAME) <= 35
    order by 2 DESC;

answered Nov 05 '22 at 14:05

OldProgrammer

12,050
4
24
45

score 1 · Answer 2 · answered Nov 06 '22 at 10:27

If you want to get the original PROD_NAME as it is in the table then don't use UPPER in the select clause...

SELECT PROD_NAME as PROD_NAME, LENGTH(PROD_NAME) as PROD_NAME_LEN
FROM PRODUCTS 
WHERE LOWER(PROD_NAME) like '%documentation%'
      -- UPPER(PROD_NAME) like '%DOCUMENTATION%' - instead of LOWER(), you can do it this way too - same result
      And LENGTH(PROD_NAME) <= 35
ORDER BY 2 DESC;

Regards...

score -1 · Answer 3 · answered Nov 05 '22 at 14:05

-1

When I use PHP with MySQL, I personly use something like:

$setgeneral=$db->prepare("SELECT * FROM general where general_id=:general_id");
$setgeneral->execute(array('general_id' => 0));
$getgeneral=$setgeneral->fetch(PDO::FETCH_ASSOC);

That code takes the "general" table and brings a row/rows that has general_id=0. Well, it is not for only id's. You can use it for prod_name too!

answered Nov 05 '22 at 14:05

Ömer Yaman

1
2

1

This has no relevance to the question OP asked. – OldProgrammer Nov 05 '22 at 14:08
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 08 '22 at 13:28

Searching for a string in a column

3 Answers3