求用SQL识别分离INT型和字符型

[xhunanpp]

求用SQL识别分离INT型和字符型

哪一个字符串为： '中国A001'
我要分开为: '中国', 'A', 001
这样怎么分啊，

[xhunanpp]

如果我有一列字段

Name
-------------------------
中国A001
美国B002
英国C003
小日本D04

分成

Name String Int
-----------------------
中国 A 001
美国 B 002
英国 C 003
小日本 D 04

[internetcsdn]

--参考:

set @in ='abc123de4'
declare @n int
declare @i int
set @n=len(@in)
set @i=1
declare @s varchar(50)
set @s=''
while @i<=@n
begin
if isnumeric(substring(@in,@i,1))=1
begin
set @s=@s+substring(@in,@i,1)
end
set @i=@i+1
end
print @s

--------
1234

[internetcsdn]

只能分离数字.

字母就不知怎办了.

[huwgao]

declare @t table(name varchar(50))
insert into @t
select '中国A001' union all
select '美国B002' union all
select '英国C003' union all
select '小日本D04'

select top 26 identity(int,65,1)xh into #t from syscolumns

select left(b.name,pos-1) as name,
substring(b.name,pos,1) as string,
right(b.name,len(b.name)-pos) as int
from (select name,charindex(char(xh),name) as pos from @t cross join #t) as a
inner join @t as b
on a.pos>0 and a.name=b.name

drop table #t

[internetcsdn]

高手.

[huwgao]

--支持中间多字符分离

declare @t table(name varchar(50))
insert into @t
select '中国A001' union all
select '美国B002' union all
select '英国C003' union all
select '小日本D04' union all
select '韩国AB04'

select top 26 identity(int,65,1)xh into #t from syscolumns

select left(b.name,minpos-1) as name,
substring(b.name,minpos,maxpos-minpos+1) as string,
right(b.name,len(b.name)-maxpos) as int
from @t as c
left outer join
(select name,max(pos) as maxpos,min(pos) as minpos
from
(select name,charindex(char(xh),name) as pos
from @t
cross join #t) as a
where a.pos<>0
group by a.name) as b
on c.name=b.name

drop table #t

[xhunanpp]

没有更好的方法吗

[huwgao]

select left(name,charindex('0',name)-2)
,substring(name,charindex('0',name)-1,1)
,right(name,len(name)-charindex('0',name)+1)
from @t

--字母后必须有0
--只能分离1个字母

[seekmoon]

mark

[zjcxc]

declare @t table(Name varchar(10))
insert @t select '中国A001'
union all select '美国B002'
union all select '英国C003'
union all select '小日本D04'
union all select '小日本处死'

select A=left(Name,patindex('%[a-z]%',Name+'a')-1)
,B=substring(Name,patindex('%[a-z]%',Name+'a')
,patindex('%[0-9]%',Name+'0')-patindex('%[a-z]%',Name+'a'))
,C=stuff(Name,1,patindex('%[0-9]%',Name+'0')-1,'')
from @t

/*--结果

A B C
---------- ---------- ----------
中国 A 001
美国 B 002
英国 C 003
小日本 D 04
小日本处死

（所影响的行数为 5 行）
--*/