Display table entries using partial column entry

On Sun, Dec 10, 2017 at 4:50 PM, Sherman Willden <operasopranos@gmail.com>
wrote:

The database table has five columns; 'id aria artist a_artist
album_title'. I assume I will use a SELECT * FROM aria_precis WHERE type
command.

I want to access all arias that have mio ben within the aria title. In the
example below I want to display cbiylm06 and cbiylm10.

cbiylm06#Caro mio ben#Cecilia Bartoli##Se tu m'ami
cbiylm07#Pur dicesti, o bocca bella#Cecilia Bartoli##Se tu m'ami
cbiylm08#Intorno all'idol mio#Cecilia Bartoli##Se tu m'ami
cbiylm09#Nel cor più non mi sento#Cecilia Bartoli##Se tu m'ami
cbiylm10#Il mio ben quando ve#Cecilia Bartoli##Se tu m'ami
cbiylm11#O Leggiadri Occhi Belli#Cecilia Bartoli##Se tu m'ami
cbiylm12#Il mio bel foco#Cecilia Bartoli##Se tu m'ami

Thank you;

Sherman

Sherman,
In the future, please be kind enough to provide PostgreSQL version and O/S
when posting to this listing.
Also include COLUMN HEADERS with all data and present the data formatted so
it is easily readable by humans.

Presuming the # is your column divider, and the data you have provided
looks like this:
id #aria #artist
#a_artist #album_title
cbiylm06 #Caro mio ben #Cecilia Bartoli
# #Se tu m'ami
cbiylm07 #Pur dicesti, o bocca bella #Cecilia Bartoli #
#Se tu m'ami
cbiylm08 #Intorno all'idol mio #Cecilia Bartoli
# #Se tu m'ami
cbiylm09 #Nel cor più non mi sento #Cecilia Bartoli #
#Se tu m'ami
cbiylm10 #Il mio ben quando ve #Cecilia Bartoli #
#Se tu m'ami
cbiylm11 #O Leggiadri Occhi Belli #Cecilia Bartoli #
#Se tu m'ami
cbiylm12 #Il mio bel foco #Cecilia Bartoli
# #Se tu m'ami

Then all you really need is:
SELECT *
FROM aria_precis
WHERE aria LIKE '%mio ben%';

Pattern Matching
https://www.postgresql.org/docs/9.6/static/functions-matching.html

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Dec 10, 2017, at 4:10 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

On Sun, Dec 10, 2017 at 4:50 PM, Sherman Willden <operasopranos@gmail.com> wrote:
The database table has five columns; 'id aria artist a_artist album_title'. I assume I will use a SELECT * FROM aria_precis WHERE type command.

I want to access all arias that have mio ben within the aria title. In the example below I want to display cbiylm06 and cbiylm10.

cbiylm06#Caro mio ben#Cecilia Bartoli##Se tu m'ami
cbiylm07#Pur dicesti, o bocca bella#Cecilia Bartoli##Se tu m'ami
cbiylm08#Intorno all'idol mio#Cecilia Bartoli##Se tu m'ami
cbiylm09#Nel cor più non mi sento#Cecilia Bartoli##Se tu m'ami
cbiylm10#Il mio ben quando ve#Cecilia Bartoli##Se tu m'ami
cbiylm11#O Leggiadri Occhi Belli#Cecilia Bartoli##Se tu m'ami
cbiylm12#Il mio bel foco#Cecilia Bartoli##Se tu m'ami

Thank you;

Sherman

Sherman,
In the future, please be kind enough to provide PostgreSQL version and O/S when posting to this listing.
Also include COLUMN HEADERS with all data and present the data formatted so it is easily readable by humans.

Presuming the # is your column divider, and the data you have provided looks like this:
id #aria #artist #a_artist #album_title
cbiylm06 #Caro mio ben #Cecilia Bartoli # #Se tu m'ami
cbiylm07 #Pur dicesti, o bocca bella #Cecilia Bartoli # #Se tu m'ami
cbiylm08 #Intorno all'idol mio #Cecilia Bartoli # #Se tu m'ami
cbiylm09 #Nel cor più non mi sento #Cecilia Bartoli # #Se tu m'ami
cbiylm10 #Il mio ben quando ve #Cecilia Bartoli # #Se tu m'ami
cbiylm11 #O Leggiadri Occhi Belli #Cecilia Bartoli # #Se tu m'ami
cbiylm12 #Il mio bel foco #Cecilia Bartoli # #Se tu m'ami

Then all you really need is:
SELECT *
FROM aria_precis
WHERE aria LIKE '%mio ben%';

Pattern Matching
https://www.postgresql.org/docs/9.6/static/functions-matching.html

Where I hop you will find the tilde operator ('~', '~*'). Why more fun.

On 11-12-17 00:39, Gmail wrote:

On Dec 10, 2017, at 4:10 PM, Melvin Davidson <melvin6925@gmail.com
<mailto:melvin6925@gmail.com>> wrote:

On Sun, Dec 10, 2017 at 4:50 PM, Sherman Willden
<operasopranos@gmail.com <mailto:operasopranos@gmail.com>> wrote:

...

Thank you;

Sherman

Sherman,
In the future, please be kind enough to provide PostgreSQL version and
O/S when posting to this listing.
Also include COLUMN HEADERS with all data and present the data
formatted so it is easily readable by humans.

Presuming the # is your column divider, and the data you have provided
looks like this:
id                #aria                                     #artist
                      #a_artist     #album_title
cbiylm06    #Caro mio ben                      #Cecilia
Bartoli         #                 #Se tu m'ami
cbiylm07    #Pur dicesti, o bocca bella    #Cecilia Bartoli        
#                 #Se tu m'ami
cbiylm08    #Intorno all'idol mio               #Cecilia
Bartoli         #                 #Se tu m'ami
cbiylm09    #Nel cor più non mi sento      #Cecilia Bartoli       
#                 #Se tu m'ami
cbiylm10    #Il mio ben quando ve           #Cecilia Bartoli        
#                 #Se tu m'ami
cbiylm11    #O Leggiadri Occhi Belli        #Cecilia Bartoli        
#                 #Se tu m'ami
cbiylm12    #Il mio bel foco                      #Cecilia
Bartoli         #                 #Se tu m'ami

Then all you really need is:
SELECT *
   FROM aria_precis
WHERE aria LIKE '%mio ben%';

Pattern Matching
https://www.postgresql.org/docs/9.6/static/functions-matching.html

Where I hop you will find the tilde operator ('~', '~*').  Why more fun.

You should be aware of case insensitivity.

LIKE '%mio ben%', will only match lower case

and, PostgreSQL-specific:
ILIKE '%mio ben%', will match any case
~ 'mio ben', will only match lower case
~* 'mio ben', will match any case