declare @tb table(col varchar(20)) insert @tb select '32323' union all select 'sdaf2' select stuff(col,patindex('%[1-9]%', col),0,' ') from @tb /* 32323 sdaf 2 */
DECLARE @t TABLE([v] NVARCHAR(6)) INSERT @t SELECT N'123' UNION ALL SELECT N'ad132' UNION ALL SELECT N'asf' UNION ALL SELECT N'232ads' /************/ /*Test Data*/ /***fcuandy**/ /*2008-11-24*/ /************/
SELECT * FROM @t /* 123 ad132 asf 232ads */ UPDATE @t SET v = STUFF(v,PATINDEX('%[0-9]%',v),1,' ') WHERE PATINDEX('%[0-9]%',v)>0 SELECT * FROM @t /* 23 ad 32 asf 32ads */
insert @tb
select '32323' union all
select 'sdaf2' select stuff(col,patindex('%[1-9]%', col),0,' ') from @tb
/*
32323
sdaf 2
*/
INSERT @t SELECT N'123'
UNION ALL SELECT N'ad132'
UNION ALL SELECT N'asf'
UNION ALL SELECT N'232ads'
/************/
/*Test Data*/
/***fcuandy**/
/*2008-11-24*/
/************/
SELECT * FROM @t
/*
123
ad132
asf
232ads
*/
UPDATE @t SET v = STUFF(v,PATINDEX('%[0-9]%',v),1,' ') WHERE PATINDEX('%[0-9]%',v)>0
SELECT * FROM @t
/*
23
ad 32
asf
32ads
*/
declare @a varchar(50)
set @a = 'abda1dfd'select stuff(@a,patindex('%[1-9]%', @a),0,space(1)) 结果:
abda 1dfd
不是1.打忘了