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中的列中格式最好。

发现有的时候方法和诀窍是很重要的，经验也是很重要的。因此，处理过了的东西，你下次处理肯定会有优先考虑那种办法。只是好坏，快慢而也，也许就是结果可能一样。