1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `test`
--------------
--------------
CREATE TABLE `test`
( `id` integer unsigned not null auto_increment primary key,
`pdesc` varchar(255) not null,
`palias` varchar(255) not null
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `test` (`pdesc`,`palias`) values
('B1CD', '\\172.16.128.100\B1CD_c_127.103_OKIC711'),
('B121', '\\172.16.128.100\B121_C_172.129_Lexmark CS510'),
('B122', '\\172.16.128.100\B122_n_127.123_ricohmp4055'),
('B119', '\\172.16.128.100\b119_c_127.108_okiC711'),
('A003', '\\172.16.128.100\A003_2_n_172.111_ricohmp6503'),
('A003', '\\172.16.128.100\A003_1_n_172.100_ricohmp6503'),
('B1LA', '\\172.16.128.100\B1LA_n_127.105_lexmark460dn'),
('C227', '\\172.16.128.100\C227_C_172.130_Lexmark CS510'),
('C230', '\\172.16.128.100\C230_C_172.131_Lexmark CS510'),
('C232', '\\172.16.128.100\C232_c_127.118_oki711'),
('C2L2', '\\172.16.128.100\C2L2_c_127.117_Brother-HL4040CN'),
('D201', '\\172.16.128.100\D201 _c_127.102_OKI C711')
--------------
--------------
select * from `test`
--------------
+----+-------+------------------------------------------------+
| id | pdesc | palias |
+----+-------+------------------------------------------------+
| 1 | B1CD | \172.16.128.100B1CD_c_127.103_OKIC711 |
| 2 | B121 | \172.16.128.100B121_C_172.129_Lexmark CS510 |
| 3 | B122 | \172.16.128.100B122_n_127.123_ricohmp4055 |
| 4 | B119 | \172.16.128.10119_c_127.108_okiC711 |
| 5 | A003 | \172.16.128.100A003_2_n_172.111_ricohmp6503 |
| 6 | A003 | \172.16.128.100A003_1_n_172.100_ricohmp6503 |
| 7 | B1LA | \172.16.128.100B1LA_n_127.105_lexmark460dn |
| 8 | C227 | \172.16.128.100C227_C_172.130_Lexmark CS510 |
| 9 | C230 | \172.16.128.100C230_C_172.131_Lexmark CS510 |
| 10 | C232 | \172.16.128.100C232_c_127.118_oki711 |
| 11 | C2L2 | \172.16.128.100C2L2_c_127.117_Brother-HL4040CN |
| 12 | D201 | \172.16.128.100D201 _c_127.102_OKI C711 |
+----+-------+------------------------------------------------+
--------------
select substring_index(palias, '_', -1) as palias
from `test`
--------------
+------------------+
| palias |
+------------------+
| OKIC711 |
| Lexmark CS510 |
| ricohmp4055 |
| okiC711 |
| ricohmp6503 |
| ricohmp6503 |
| lexmark460dn |
| Lexmark CS510 |
| Lexmark CS510 |
| oki711 |
| Brother-HL4040CN |
| OKI C711 |
+------------------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager