CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldeveloper
Jony_Junior
Jony_Junior

Reputation: 35

Searching for a string in a column

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.sstatic.net/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

Upvotes: 0

Views: 80

Answers (3)

d r
d r

Reputation: 7786

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...

Upvotes: 1

&#214;mer Yaman
&#214;mer Yaman

Reputation: 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!

Upvotes: -1

OldProgrammer
OldProgrammer

Reputation: 12169

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;

Upvotes: 2

Related Questions