if object_id('[t_revpaydata]') is not null drop table [t_revpaydata] go create table [t_revpaydata]([pay_prem_dur] varchar(2)) insert [t_revpaydata] select '0' union all select '00' union all select '01' union all select '02' union all select '03' union all select '04' union all select '05' union all select '06' union all select '07' union all select '08' union all select '09' union all select '1' union all select '10' union all select '11' union all select '12' union all select '13' union all select '14' union all select '15' union all select '16' union all select '17' union all select '18' union all select '19'select cast([pay_prem_dur] as int) as [pay_prem_dur] from [t_revpaydata] drop table [t_revpaydata]
if object_id('[t_revpaydata]') is not null drop table [t_revpaydata] go create table [t_revpaydata]([pay_prem_dur] varchar(2)) insert [t_revpaydata] select '0' union all select '00' union all select '01' union all select '02' union all select '03' union all select '04' union all select '05' union all select '06' union all select '07' union all select '08' union all select '09' union all select '1' union all select '10' union all select '11' union all select '12' union all select '13' union all select '14' union all select '15' union all select '16' union all select '17' union all select '18' union all select '19'select cast([pay_prem_dur] as int) as [pay_prem_dur] from [t_revpaydata] drop table [t_revpaydata] /* pay_prem_dur ------------ 0 0 1 2 3 4 5 6 7 8 9 1 10 11 12 13 14 15 16 17 18 19(22 row(s) affected)
--> 测试数据: @tb declare @tb table (pay_prem_dur varchar(2)) insert into @tb select '0' union all select '00' union all select '01' union all select '02' union all select '03' union all select '04' union all select '05' union all select '06' union all select '07' union all select '08' union all select '09' union all select '1' union all select '10' union all select '11' union all select '12' union all select '13' union all select '14' union all select '15' union all select '16' union all select '17' union all select '18' union all select '19'select nn=case when left(pay_prem_dur,1)='0' then right(pay_prem_dur,len(pay_prem_dur)-1) else pay_prem_dur end from @tbnn ----0 1 2 3 4 5 6 7 8 9 1 10 11 12 13 14 15 16 17 18 19(22 行受影响)
SELECT REPLACE(pay_prem_dur,'0','') FROM @tb
select convert(int,pay_prem_dur)from @tb or select case left(pay_prem_dur,1) when 0 then replace(pay_prem_dur,'0','') else pay_prem_dur end from @tb
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-11-17 16:34:12 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[pay_prem_dur] if object_id('[pay_prem_dur]') is not null drop table [pay_prem_dur] go create table [pay_prem_dur]([col] varchar(2)) insert [pay_prem_dur] select '0' union all select '00' union all select '01' union all select '02' union all select '03' union all select '04' union all select '05' union all select '06' union all select '07' union all select '08' union all select '09' union all select '1' union all select '10' union all select '11' union all select '12' union all select '13' union all select '14' union all select '15' union all select '16' union all select '17' union all select '18' union all select '19' --------------开始查询--------------------------select cast (col as int) as col from [pay_prem_dur] ----------------结果---------------------------- /* col ----------- 0 0 1 2 3 4 5 6 7 8 9 1 10 11 12 13 14 15 16 17 18 19(22 行受影响) */
select li=case left(pay_prem_dur,1) when 0 then substring(pay_prem_dur,2,1) else pay_prem_dur end from @tb
1、方法一: select distinct(replace( pay_prem_dur,'0','')) from t_revpaydata 2、方法二 select case left(pay_prem_dur,1) when 0 then replace(pay_prem_dur,'0','') else pay_prem_dur end from t_revpaydata
if object_id('[t_revpaydata]') is not null drop table [t_revpaydata]
go
create table [t_revpaydata]([pay_prem_dur] varchar(2))
insert [t_revpaydata]
select '0' union all
select '00' union all
select '01' union all
select '02' union all
select '03' union all
select '04' union all
select '05' union all
select '06' union all
select '07' union all
select '08' union all
select '09' union all
select '1' union all
select '10' union all
select '11' union all
select '12' union all
select '13' union all
select '14' union all
select '15' union all
select '16' union all
select '17' union all
select '18' union all
select '19'select cast([pay_prem_dur] as int) as [pay_prem_dur]
from [t_revpaydata]
drop table [t_revpaydata]
if object_id('[t_revpaydata]') is not null drop table [t_revpaydata]
go
create table [t_revpaydata]([pay_prem_dur] varchar(2))
insert [t_revpaydata]
select '0' union all
select '00' union all
select '01' union all
select '02' union all
select '03' union all
select '04' union all
select '05' union all
select '06' union all
select '07' union all
select '08' union all
select '09' union all
select '1' union all
select '10' union all
select '11' union all
select '12' union all
select '13' union all
select '14' union all
select '15' union all
select '16' union all
select '17' union all
select '18' union all
select '19'select cast([pay_prem_dur] as int) as [pay_prem_dur]
from [t_revpaydata]
drop table [t_revpaydata]
/*
pay_prem_dur
------------
0
0
1
2
3
4
5
6
7
8
9
1
10
11
12
13
14
15
16
17
18
19(22 row(s) affected)
declare @tb table (pay_prem_dur varchar(2))
insert into @tb
select '0' union all
select '00' union all
select '01' union all
select '02' union all
select '03' union all
select '04' union all
select '05' union all
select '06' union all
select '07' union all
select '08' union all
select '09' union all
select '1' union all
select '10' union all
select '11' union all
select '12' union all
select '13' union all
select '14' union all
select '15' union all
select '16' union all
select '17' union all
select '18' union all
select '19'select nn=case when left(pay_prem_dur,1)='0' then right(pay_prem_dur,len(pay_prem_dur)-1) else pay_prem_dur end
from @tbnn
----0
1
2
3
4
5
6
7
8
9
1
10
11
12
13
14
15
16
17
18
19(22 行受影响)
or
select case left(pay_prem_dur,1) when 0 then replace(pay_prem_dur,'0','') else pay_prem_dur end from @tb
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-17 16:34:12
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[pay_prem_dur]
if object_id('[pay_prem_dur]') is not null drop table [pay_prem_dur]
go
create table [pay_prem_dur]([col] varchar(2))
insert [pay_prem_dur]
select '0' union all
select '00' union all
select '01' union all
select '02' union all
select '03' union all
select '04' union all
select '05' union all
select '06' union all
select '07' union all
select '08' union all
select '09' union all
select '1' union all
select '10' union all
select '11' union all
select '12' union all
select '13' union all
select '14' union all
select '15' union all
select '16' union all
select '17' union all
select '18' union all
select '19'
--------------开始查询--------------------------select cast (col as int) as col from [pay_prem_dur]
----------------结果----------------------------
/* col
-----------
0
0
1
2
3
4
5
6
7
8
9
1
10
11
12
13
14
15
16
17
18
19(22 行受影响)
*/
select distinct(replace( pay_prem_dur,'0','')) from t_revpaydata
2、方法二
select case left(pay_prem_dur,1) when 0 then replace(pay_prem_dur,'0','') else pay_prem_dur end from t_revpaydata
方法2:可以在不改变字段类型的前提下使用REPLACE函数
下面LZ参考一下,也可以直接看看联机丛书里面有例子
http://www.google.cn/search?hl=zh-CN&client=aff-maxthon&channel=channel4&source=hp&hs=X5k&newwindow=1&q=sql+replace&aq=f&oq=