Question

sql server 中 长数字隔三位用逗号"，"隔开怎么做

Answer 1

if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,price varchar(100),customer int,cinvcode int)
insert into #tb
select 1,'1.5',4,2 union all
select 2,'3.5,6',3,2 union all
select 3,'2.5,3',6,1 union all
select 4,'5',1,5
select id=row_number()over(order by getdate()),
customer,
cinvcode,
price=substring(price+',',number,charindex(',',price+',',number)-number)
from #tb ,master..spt_values s
where type='p' and number>0 and substring(','+price,number,1)=','