  SQL. 18    
 


     SQL     .  18    : trim, lower, upper, replace, substr, instr, length, LIKE,  email, , , ,      .     SQLite,     .





 

  SQL. 18    





  





   


        ,  ,        .

     :     email    ,       .

     : , , ,        .




  


     ,   SELECT.     SQLite,          .

     .      replace,    ,    .

      :  ,     .




  


1.    email (#task-1)

2.  email    (#task-2)

3.  email   @ (#task-3)

4.       (#litres_trial_promo)

5.    (#litres_trial_promo)

6.      (#litres_trial_promo)

7.    (#litres_trial_promo)

8.    email (#litres_trial_promo)

9.  email   (#litres_trial_promo)

10.      (#litres_trial_promo)

11.       (#litres_trial_promo)

12.      (#litres_trial_promo)

13.      (#litres_trial_promo)

14.      (#litres_trial_promo)

15.     (#litres_trial_promo)

16.    (#litres_trial_promo)

17.     (#litres_trial_promo)

18.      (#litres_trial_promo)




 





 1.    email



 

  email   ,           .




CREATE TABLE clients(id INTEGER, email TEXT);

INSERT INTO clients VALUES

(1,' anna@example.test '),

(2,'oleg@example.test'),

(3,'  nina@example.test');




SELECT id, trim(email) AS clean_email

FROM clients

ORDER BY id;


 

[[1, "anna@example.test"], [2, "oleg@example.test"], [3, "nina@example.test"]]


 

trim(email)     .

  email  .

ORDER BY    .


  

trim     .      email,    .


 

  2    trim,      ?

:   2       email,      .




 2.  email   



 

   ,   email    .




CREATE TABLE clients(id INTEGER, email TEXT);

INSERT INTO clients VALUES

(1,'Anna@Example.Test'),

(2,'oleg@example.test'),

(3,'NINA@EXAMPLE.TEST');




SELECT id, lower(email) AS normalized_email

FROM clients

ORDER BY id;


 

[[1, "anna@example.test"], [2, "oleg@example.test"], [3, "nina@example.test"]]


 

lower     .

  email   .

 email    .


  

    email,          lower.


 

 lower      email   ?

:             .




 3.  email   @



 

   ,      email,    .




CREATE TABLE contacts(id INTEGER, email TEXT);

INSERT INTO contacts VALUES

(1,'a@example.test'),

(2,'wrong-email'),

(3,'n@example.test'),

(4,'empty');




SELECT id, email

FROM contacts




  .


   .

   ,     (https://www.litres.ru/pages/biblio_book/?art=74419712)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .


