if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GOCREATE TABLE [dbo].[test] (
[item] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[qty] [int] NULL ,
[pp] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
insert into test
select '201',10,'999999'
------------------------------------------建函數--------------
Create function testp(@s varchar(10))
returns varchar(20)
as
begin
declare @k varchar(20)
declare @p varchar(10)
set @k=''
while len(@s)>3
begin
set @k=@k+','+right(@s,3)
set @s=substring(@s,1,len(@s)-3)
end
set @k=@s+@k
return(@k)
end
----------------------------end-------------------------------------
--------------------------輸出-在sql中調用函數 testp---------------------------
select *,dbo.testp(ltrim(pp)) from test---------------------------結果是-------------
201 10 999999 999,999
-----------------------刪除函數-------------
drop function testp
drop table [dbo].[test]
GOCREATE TABLE [dbo].[test] (
[item] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[qty] [int] NULL ,
[pp] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
insert into test
select '201',10,'999999'
------------------------------------------建函數--------------
Create function testp(@s varchar(10))
returns varchar(20)
as
begin
declare @k varchar(20)
declare @p varchar(10)
set @k=''
while len(@s)>3
begin
set @k=@k+','+right(@s,3)
set @s=substring(@s,1,len(@s)-3)
end
set @k=@s+@k
return(@k)
end
----------------------------end-------------------------------------
--------------------------輸出-在sql中調用函數 testp---------------------------
select *,dbo.testp(ltrim(pp)) from test---------------------------結果是-------------
201 10 999999 999,999
-----------------------刪除函數-------------
drop function testp
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GOCREATE TABLE [dbo].[test] (
[item] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[qty] [int] NULL ,
[pp] [char] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
insert into test
select '201',10,'999999'
------------------------------------------建函數--------------
Create function testp(@s char(20))
returns varchar(20)
as
begin
declare @k varchar(20)
declare @p char(10)
declare @s1 varchar(20)
set @k=''
set @s1=rtrim(@s)
while len(rtrim(@s1))>3
begin
set @k=@k+','+right(rtrim(@s1),3)
set @s1=substring(rtrim(@s1),1,len(@s1)-3)
end
set @k=@s1+@k
return (@k)
end
----------------------------end-------------------------------------
--------------------------輸出-在sql中調用函數 testp---------------------------
select *,dbo.testp(rtrim(pp)) from test---------------------------結果是-------------
201 10 999999 999,999
-----------------------刪除函數-------------
drop function testpselect dbo.testp('99999')