疑难SQL查询

[sllwd3]

有个字段是VARCHAR类型的，数据如下：
MaterialGroup
21
23
112
w1
测试1
34
4567
1002
我想把这个字段中能转化为int的长度不够4位的前面补0，不是int的不变，然后输出，怎么做到吖？
输出结果如：
0021
0023
0112
w1
测试1
0034
4567
1002

[Tiger_Zhao]

WITH T(MaterialGroup) AS (
    SELECT '21' UNION ALL
    SELECT '23' UNION ALL
    SELECT '112' UNION ALL
    SELECT 'w1' UNION ALL
    SELECT '测试1' UNION ALL
    SELECT '34' UNION ALL
    SELECT '4567' UNION ALL
    SELECT '1002' 
)
SELECT CASE WHEN MaterialGroup like '%[^0-9]%' THEN
            MaterialGroup
       ELSE
           RIGHT('000'+MaterialGroup,4)
       END MaterialGroup
 FROM T

MaterialGroup
-------------
0021
0023
0112
w1
测试1
0034
4567
1002

[--小F--]

select
   case when isnumeric(cast(MaterialGroup as int))=1 then right('00000'+MaterialGroup,4) else MaterialGroup end 
from
   tb

[Tiger_Zhao]

引用 2 楼 fredrickhu 的回复:

select
   case when isnumeric(cast(MaterialGroup as int))=1 then right('00000'+MaterialGroup,4) else MaterialGroup end 
from
   tb

非数字的 cast(MaterialGroup as int) 就出错了

[wtujedp]

create table t1 ( MaterialGroup varchar(20) ) insert into t1 values('21') insert into t1 values('23') insert into t1 values('112') insert into t1 values('w1') insert into t1 values('测试1') insert into t1 values('4567') insert into t1 values('1002') create function PadLeft ( @str varchar(100), @num int, @char varchar(1) ) returns varchar(100) as begin declare @return varchar(100); set @return=@str; if (@char='' or @char is null ) set @char=' '; if (@num is null) set @num=0; while LEN(@return)<@num begin set @return=@char+@return; end return @return; end select (case when ISNUMERIC(MaterialGroup)=1 then dbo.PadLeft(MaterialGroup,4,'0') else MaterialGroup end) from t1

[lene3]

select case when isnumeric(MaterialGroup)=1 then replicate('0',4-len(MaterialGroup))+MaterialGroup else MaterialGroup end from tb

[sqlkxr]

insert into test(fm) values('21')

insert into test values('23')

insert test values('112')

insert into test 
select 'w1'

union all
select '测试1'
union all
select '34'
union all
select '4567'
union all
select '1002'


select case isnumeric(fm) when 1 then  case when len(fm)<4 then right('0000'+fm,4) else fm end else fm end from test


                                                   
-------------------------------------------------- 
0021
0023
0112
w1
测试1
0034
4567
1002

（所影响的行数为 8 行）