SQL/MySQL/Function/Replace

Материал из SQL эксперт
Перейти к: навигация, поиск

Call concat and replace

   <source lang="sql">

/* mysql> select * from sales; +-----+------------+--------+--------+--------+------+------------+ | num | name | winter | spring | summer | fall | category | +-----+------------+--------+--------+--------+------+------------+ | 1 | Java | 1067 | 200 | 150 | 267 | Holiday | | 2 | C | 970 | 770 | 531 | 486 | Profession | | 3 | JavaScript | 53 | 13 | 21 | 856 | Literary | | 4 | SQL | 782 | 357 | 168 | 250 | Profession | | 5 | Oracle | 589 | 795 | 367 | 284 | Holiday | | 6 | MySQL | 953 | 582 | 336 | 489 | Literary | | 7 | Cplus | 752 | 657 | 259 | 478 | Literary | | 8 | Python | 67 | 23 | 83 | 543 | Holiday | | 9 | PHP | 673 | 48 | 625 | 52 | Profession | +-----+------------+--------+--------+--------+------+------------+ 9 rows in set (0.01 sec) mysql> SELECT CONCAT((REPLACE(name, "_", " ")), " ", "Sale") AS "Sales Plan" 

   ->   FROM sales WHERE category!="Profession";

+-----------------+ | Sales Plan | +-----------------+ | Java Sale | | JavaScript Sale | | Oracle Sale | | MySQL Sale | | Cplus Sale | | Python Sale | +-----------------+ 6 rows in set (0.00 sec)

  • /

Drop table sales;

CREATE TABLE sales( 

   num MEDIUMINT NOT NULL AUTO_INCREMENT,
   name CHAR(20),
   winter INT,
   spring INT,
   summer INT,
   fall INT,
   category CHAR(13),
   primary key(num)

)type=MyISAM;

insert into sales value(1, "Java", 1067 , 200, 150, 267,"Holiday"); insert into sales value(2, "C",970,770,531,486,"Profession"); insert into sales value(3, "JavaScript",53,13,21,856,"Literary"); insert into sales value(4, "SQL",782,357,168,250,"Profession"); insert into sales value(5, "Oracle",589,795,367,284,"Holiday"); insert into sales value(6, "MySQL",953,582,336,489,"Literary"); insert into sales value(7, "Cplus",752,657,259,478,"Literary"); insert into sales value(8, "Python",67,23,83,543,"Holiday"); insert into sales value(9, "PHP",673,48,625,52,"Profession"); select * from sales;

SELECT CONCAT((REPLACE(name, "_", " ")), " ", "Sale") AS "Sales Plan" 

 FROM sales WHERE category!="Profession";
          
      </source>


Call REPLACE function in select clause

   <source lang="sql">

/* mysql> select * from sales; +-----+------------+--------+--------+--------+------+------------+ | num | name | winter | spring | summer | fall | category | +-----+------------+--------+--------+--------+------+------------+ | 1 | Java | 1067 | 200 | 150 | 267 | Holiday | | 2 | C | 970 | 770 | 531 | 486 | Profession | | 3 | JavaScript | 53 | 13 | 21 | 856 | Literary | | 4 | SQL | 782 | 357 | 168 | 250 | Profession | | 5 | Oracle | 589 | 795 | 367 | 284 | Holiday | | 6 | MySQL | 953 | 582 | 336 | 489 | Literary | | 7 | Cplus | 752 | 657 | 259 | 478 | Literary | | 8 | Python | 67 | 23 | 83 | 543 | Holiday | | 9 | PHP | 673 | 48 | 625 | 52 | Profession | +-----+------------+--------+--------+--------+------+------------+ 9 rows in set (0.01 sec) mysql> SELECT REPLACE(name, "_", " ") FROM sales; +-------------------------+ | REPLACE(name, "_", " ") | +-------------------------+ | Java | | C | | JavaScript | | SQL | | Oracle | | MySQL | | Cplus | | Python | | PHP | +-------------------------+ 9 rows in set (0.01 sec)

  • /

Drop table sales;

CREATE TABLE sales( 

   num MEDIUMINT NOT NULL AUTO_INCREMENT,
   name CHAR(20),
   winter INT,
   spring INT,
   summer INT,
   fall INT,
   category CHAR(13),
   primary key(num)

)type=MyISAM;

insert into sales value(1, "Java", 1067 , 200, 150, 267,"Holiday"); insert into sales value(2, "C",970,770,531,486,"Profession"); insert into sales value(3, "JavaScript",53,13,21,856,"Literary"); insert into sales value(4, "SQL",782,357,168,250,"Profession"); insert into sales value(5, "Oracle",589,795,367,284,"Holiday"); insert into sales value(6, "MySQL",953,582,336,489,"Literary"); insert into sales value(7, "Cplus",752,657,259,478,"Literary"); insert into sales value(8, "Python",67,23,83,543,"Holiday"); insert into sales value(9, "PHP",673,48,625,52,"Profession"); select * from sales;

SELECT REPLACE(name, "_", " ") FROM sales; 

      </source>


Do calculation in select clause

   <source lang="sql">

/* mysql> select * from sales; +-----+------------+--------+--------+--------+------+------------+ | num | name | winter | spring | summer | fall | category | +-----+------------+--------+--------+--------+------+------------+ | 1 | Java | 1067 | 200 | 150 | 267 | Holiday | | 2 | C | 970 | 770 | 531 | 486 | Profession | | 3 | JavaScript | 53 | 13 | 21 | 856 | Literary | | 4 | SQL | 782 | 357 | 168 | 250 | Profession | | 5 | Oracle | 589 | 795 | 367 | 284 | Holiday | | 6 | MySQL | 953 | 582 | 336 | 489 | Literary | | 7 | Cplus | 752 | 657 | 259 | 478 | Literary | | 8 | Python | 67 | 23 | 83 | 543 | Holiday | | 9 | PHP | 673 | 48 | 625 | 52 | Profession | +-----+------------+--------+--------+--------+------+------------+ 9 rows in set (0.01 sec) mysql> SELECT REPLACE(name, "_", " ") AS Outfit, 

   -> winter + spring + summer + fall AS "Yearly Sales",
   -> category AS Category
   -> FROM sales;

+------------+--------------+------------+ | Outfit | Yearly Sales | Category | +------------+--------------+------------+ | Java | 1684 | Holiday | | C | 2757 | Profession | | JavaScript | 943 | Literary | | SQL | 1557 | Profession | | Oracle | 2035 | Holiday | | MySQL | 2360 | Literary | | Cplus | 2146 | Literary | | Python | 716 | Holiday | | PHP | 1398 | Profession | +------------+--------------+------------+ 9 rows in set (0.00 sec)

  • /

Drop table sales;

CREATE TABLE sales( 

   num MEDIUMINT NOT NULL AUTO_INCREMENT,
   name CHAR(20),
   winter INT,
   spring INT,
   summer INT,
   fall INT,
   category CHAR(13),
   primary key(num)

)type=MyISAM;

insert into sales value(1, "Java", 1067 , 200, 150, 267,"Holiday"); insert into sales value(2, "C",970,770,531,486,"Profession"); insert into sales value(3, "JavaScript",53,13,21,856,"Literary"); insert into sales value(4, "SQL",782,357,168,250,"Profession"); insert into sales value(5, "Oracle",589,795,367,284,"Holiday"); insert into sales value(6, "MySQL",953,582,336,489,"Literary"); insert into sales value(7, "Cplus",752,657,259,478,"Literary"); insert into sales value(8, "Python",67,23,83,543,"Holiday"); insert into sales value(9, "PHP",673,48,625,52,"Profession"); select * from sales;

SELECT REPLACE(name, "_", " ") AS Outfit, winter + spring + summer + fall AS "Yearly Sales", category AS Category FROM sales; 

      </source>


MySQL replace command

   <source lang="sql">

Drop table CDs; CREATE TABLE CDs ( 

  CDID SMALLINT NOT NULL PRIMARY KEY,
  CDName VARCHAR(50) NOT NULL,
  InStock SMALLINT UNSIGNED NOT NULL,
  Category VARCHAR(20)

);

INSERT INTO CDs VALUES (101, "Blood", 10, "Rock"), 

      (102, "Jazz", 17, "Jazz"),
      (103, "Class", 9, "Classical"),
      (104, "Violin", 24, NULL),
      (105, "Blues", 2, "Blues"),
      (106, "Tires", 12, "Country"),
      (107, "Essence", 5, "New Age"),
      (108, "Magic", 42, "Classical"),
      (109, "Name", 20, "Opera"),
      (110, "Fire", 23, "Country"),
      (111, "Live", 18, "Jazz"),
      (112, "Blues", 22, "Blues"),
      (113, "Stages", 42, "Blues");

CREATE TABLE CDs2a ( 

  CDID SMALLINT NOT NULL PRIMARY KEY,
  CDName VARCHAR(5) NOT NULL,
  InStock SMALLINT UNSIGNED NOT NULL

) SELECT CDID, CDName, InStock FROM CDs WHERE Category="Blues" OR Category="Jazz";

REPLACE INTO CDs2 SELECT CDID, CDName, InStock FROM CDs WHERE Category="Country" OR Category="Rock";

select * from CDs2; 

      </source>
Источник — «http://sqle.ru/index.php?title=SQL/MySQL/Function/Replace&oldid=5219»