几个SQL 的实例

一、 --某个数据库中，有些数据，为了方便使用，把某几个字母替换
--如：IO0011中把I替换成1 ，把O替换成0
use aa
go
create table card
(
 id int   not null primary key,
 password varchar(10)
)values(1,'ooi22i')
insert into card (id,password)
select 2 ,'o100ii2' union
select 4 ,'oo0i1i2' union
select 3 ,'o1a0ii2' union
select 6 ,'os0i1i2' union
select 5 ,'o300ii2'
select * from card1

结果:

1  ooi22i 

2 o100ii2 3
3 o1a0ii2 4
4 oo0i1i2 5
5 o300ii2 6
6 os0i1i2 

select replace(replace('o100ii2','i','1'),'o','0')
select replace(replace(password,'i','1'),'o','0')from card
select * into card1 from card
update card
set password=replace(replace(password,'i','1'),'o','0')
select id ,password from card

运行结果:

1 001221
2 0100112
3 01a0112
4 0001112
5 0300112
6 0s01112
二、排序13-1，13-4、14-0、14-112、12-2，。。。把-两侧的数都进行排序
create table px
(id int primary key ,
 shuju varchar(20)
)
insert into  px (id,shuju)
select 1,'13-1'union
select 2,'13-4'union
select 3,'13-114'union
select 4,'13-2'union
select 5,'14-1'union
select 6,'14-3'union
select 7,'14-14'union
select 8,'14-0'union
select 9,'113-1'union
select 10,'3-4'union
select 11,'3-114'union
select 12,'3-2'union
select 13,'15-1'union
select 15,'16-4'union
select 14,'1-114'union
select 16,'2-2'
select left('13-1',2)
select left('113-1',charindex('-','113-1')-1)--左边数据
select left (shuju,charindex('-',shuju)-1) as 左边数据 from px
select right('113-1',len('113-1')-charindex('-','113-1'))--右边数据
select right (shuju,len(shuju)-charindex('-',shuju)) as 右边数据 from px
select charindex('-','13-1')
select  *  from  px
order by Convert(int,(select left (shuju,charindex('-',shuju)-1))),Convert(int,(select right(shuju,len(shuju)-charindex('-',shuju))))

ID 数据
14 1-114
16 2-2
12 3-2
10 3-4
11 3-114
1 13-1
4 13-2
2 13-4
3 13-114
8 14-0
5 14-1
6 14-3
7 14-14
13 15-1
15 16-4
9 113-1