☆用心生活☆-wonder-{改变自己才能让自己过上不一样的日子。为自己加油!}

MSSQL自定义函数之数据格式化为千分位格式

  因为ASP WEB程序转成WINFORM程序,由于数据格式的处理不同导致了一些处理需求重新处理.  

  由于有个需求,需要将一个数字格式化为货币型,但是货币型一般是2位小数, 不是1位,或者3位,甚至更多,不过我这个后面小数位数大于3位的,我没处理,也没千分位分开,就是用逗号分开。比如:123456789.156789,我现在可能需要格式化为:123,456,789.2(1位小数),或者132,465,789.157(3位小数),也有可能是123,456,789.16(2位小数)等形式。

  先分析:1.当数据本身转成1,2,0个小数位的时候,都是可以直接转成货币型,然后,截取小数点。

              2.当数据本身需要转成3位或者更多的时候麻烦就出来了,不能采用1,不过会采用1的大部分,先截取整数部分,转成千分位,然后再把小数位数截取出来,连接成字符串,之后就得到了这样的类型。

分析CODE:

--数据转成货币格式,就是千分位逗号,小数位数之类分析
declare @inputNumber  nvarchar(30)
           
set @inputNumber='123246.456778'

--比如三位小数
--
直接转成数字位数
SELECT CONVERT(NUMERIC(18,3),@inputNumber)
           
--SELECT CONVERT(MONEY,CONVERT(NUMERIC(18,1),@inputNumber))

--
SELECT SUBSTRING('WONDER.abc',0,CHARINDEX('.','WONDER.abc'))
--
再截取整数
select SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber))
,
0

,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))
)
--截取小数部分

select SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber))
,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))+1

,
len(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))-charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))
)
--将整数部分转成数字

select
CONVERT(money,SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber))
,
0

,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))
),
1
)
--整数部分转成货币千分位

select
convert(nvarchar(30),
CONVERT(money,SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber
))
,
0

,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))
)),
1
)
--

--截取转成货币千分位后的整数部分
SELECT
SUBSTRING(convert(nvarchar(30),
CONVERT(money,SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber
))
,
0

,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))
)),
1),0
,
charindex('.'
,
convert(nvarchar(30
),
CONVERT(money,SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber
))
,
0

,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))
)),
1
))
)

--合并小数位

    SELECT
   
SUBSTRING(convert(nvarchar(30),
   
CONVERT(money,SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber
))
    ,
0

    ,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))
    )),
1),0
,
   
charindex('.'
,
   
convert(nvarchar(30
),
   
CONVERT(money,SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber
))
    ,
0

    ,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))
    )),
1
))
    )
   
+'.'

   
+SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber))
    ,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))+1

    ,
len(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))-charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))
    )

分析完毕自己创建自定义函数 :

--格式化数据为千分位等格式,比如:1245.56678,144,5.57
--
参数:@inputNumbe输入字符,@floatCount小数位数,0,1,2,3,4
--
时间:2011-04-13
ALTER function FormatNumeric(@inputNumber nvarchar(30) , @floatCount int)
RETURNS nvarchar(30
)
AS

BEGIN
   
DECLARE @strTemp NVARCHAR(30)
   
--1个小数位

    IF(@floatCount=1)
       
BEGIN

           
--测试数据
            --declare @inputNumber  nvarchar(30)
            --set @inputNumber='123246.456'
            --SELECT CONVERT(NUMERIC(18,1),@inputNumber)
            --SELECT CONVERT(MONEY,CONVERT(NUMERIC(18,1),@inputNumber))
            --SELECT CONVERT(NVARCHAR(30) , CONVERT(MONEY,CONVERT(NUMERIC(18,1),@inputNumber)) ,1)

           
--SELECT charindex('.','156.23',0)
            --SELECT charindex('.','123,246.50',0)+1
            --SELECT SUBSTRING('123,246.50',1,charindex('.','123,246.50',0)+1)
            SET @strTemp=
            
SUBSTRING(CONVERT(NVARCHAR(30) ,
                            
CONVERT(MONEY,CONVERT(NUMERIC(18,1),@inputNumber)) ,1
)
                                ,
1
,
                            
CHARINDEX('.',CONVERT(NVARCHAR(30) ,CONVERT(MONEY,CONVERT(NUMERIC(18,1),@inputNumber)) ,1),0)+1
)
           
RETURN @strTemp

       
END
   
--2个小数位
    IF(@FLOATcount=2)
       
BEGIN

           
--SELECT CONVERT(NVARCHAR(30) , CONVERT(MONEY,CONVERT(NUMERIC(18,2),@inputNumber)) ,1)
            SET @strTemp=
            
SUBSTRING(CONVERT(NVARCHAR(30) ,
                            
CONVERT(MONEY,CONVERT(NUMERIC(18,2),@inputNumber)) ,1
)
                                ,
1
,
                            
CHARINDEX('.',CONVERT(NVARCHAR(30) ,CONVERT(MONEY,CONVERT(NUMERIC(18,2),@inputNumber)) ,1),0)+2
)
           
RETURN @strTemp

       
END
--3.无小数
    IF(@FLOATcount=0)
       
BEGIN

       
--SELECT CONVERT(NVARCHAR(30) , CONVERT(MONEY,CONVERT(NUMERIC(18,0),@inputNumber)) ,1)
        SET @strTemp=
        
SUBSTRING(CONVERT(NVARCHAR(30) ,CONVERT(MONEY,CONVERT(NUMERIC(18,0),@inputNumber)) ,1)
                            ,
0
,
                        
CHARINDEX('.',CONVERT(NVARCHAR(30) ,CONVERT(MONEY,CONVERT(NUMERIC(18,0),@inputNumber)) ,1),0
))
       
RETURN @strTemp

   
END
--默认位3位小数
    ELSE
    
BEGIN
    
SET @strTemp=
       
SUBSTRING(convert(nvarchar(30),
           
CONVERT(money,SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber
))
            ,
0

            ,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))
            )),
1),0
,
           
charindex('.'
,
           
convert(nvarchar(30
),
           
CONVERT(money,SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber
))
            ,
0

            ,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))
            )),
1
))
            )
           
+'.'

           
+SUBSTRING(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber))
            ,
charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))+1

            ,
len(convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))-charindex('.',convert(nvarchar(30) ,CONVERT(NUMERIC(18,3),@inputNumber)))   
            )
    
END

return @strTemp
END


--SELECT dbo.FormatNumeric(Fld0055,2) from Pur0099
--
SELECT dbo.FormatNumeric(Fld0055,1) from Pur0099
--后记:
不过在MS的开发环境中,最好还是用string类的函数进行数据格式化,这样速度很快,不会拖累服务器,比如:12346789.15678,格式化为:12346789.15678.ToString('N1'),N1,N2,表示1,2个小数位,因此,在DATAGRIVEW中处理格式化,那就在数据源DATATABLE中的列中格式最好。
发现有的时候方法和诀窍是很重要的,经验也是很重要的。因此,处理过了的东西,你下次处理肯定会有优先考虑那种办法。只是好坏,快慢而也,也许就是结果可能一样。

posted @ 2011-04-13 14:45  ☆用心生活☆  阅读(4232)  评论(2编辑  收藏  举报
本作者写的任何文章均属原创,若要转载,请注明出处;若
进行与文章有关的商业行为,本作者保留拥有诉诸法律追究
侵权的任何行为。文章只代表本人某一学习时间段内对某一
技术的说明,不代表具有绝对的正确性,若要学习,请与各
位高手编程人员讨论学习。本文章也不代表自己某一时期某
一公司的任何专著和机密文件。仅仅提供自我学习和他人学
习的文章参考,以及自我知识记录。wanzegui325#163.com