mysql> CREATE TABLE auto (
-> num VARCHAR(10) DEFAULT NULL
-> )ENGINE=MyISAM;Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> insert into auto values('x111xx177');
Query OK, 1 row affected (0.00 sec)
mysql> insert into auto values('aj45hn67nn');
Query OK, 1 row affected (0.00 sec)
mysql> insert into auto values('sfde45njh5');
Query OK, 1 row affected (0.00 sec)
mysql> insert into auto values('5fv67yy78u');
Query OK, 1 row affected (0.00 sec)
mysql> insert into auto values('aaas23dsdd');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> delimiter //
mysql> CREATE DEFINER = CURRENT_USER FUNCTION `digital`(param1 varchar(10))
-> RETURNS varchar(10)
-> NOT DETERMINISTIC
-> CONTAINS SQL
-> SQL SECURITY DEFINER
-> COMMENT ''
-> begin
-> declare a int default 1;
-> declare b int default 0;
-> declare c int default 0;
-> declare d varchar(10) default '';
-> set b = length(param1);
-> while a <= b do
-> set c = substring(param1,a,1) regexp '[a-z]';
-> if not c then set d = concat(d,substring(param1,a,1)); end if;
-> set a = a + 1;
-> end while;
-> return d;
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> select * from auto;
+------------+
| num |
+------------+
| x111xx177 |
| aj45hn67nn |
| sfde45njh5 |
| 5fv67yy78u |
| aaas23dsdd |
+------------+
5 rows in set (0.00 sec)
mysql>
mysql> select digital(num) as auto from auto order by auto;
+--------+
| auto |
+--------+
| 111177 |
| 23 |
| 455 |
| 4567 |
| 56778 |
+--------+
5 rows in set (0.01 sec)
mysql>
Вариант для распечатки
WEB технологии (Public)
(??) on 14-Янв-09, 22:09 
