中国数字(欢迎验错)

新建数据库后，请增加两个命名空间：
USE [LzmTWWorks]
GO
CREATE SCHEMA [Private] AUTHORIZATION [dbo]
GO
CREATE SCHEMA [Helper] AUTHORIZATION [dbo]

本帖最后由 lzmtw 于 2007-12-12 18:55:57 编辑

数字与数字字符的相互转换[Helper].[ChineseNum]
USE [LzmTWWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Helper].[ChineseNum]
(
@Content nvarchar(Max)
,@Convert bit = 0
)
RETURNS nvarchar(Max)
AS
BEGIN
DECLARE
@Result nvarchar(MAX)
,@Part nvarchar(1)
,@Length smallint
,@Index smallint
,@TmpChar nvarchar(1)
,@TmpValue bigint SET @Result = N''
SET @Length = LEN(@Content)
SET @Index =1 WHILE @Index <= @Length
BEGIN
SET @Part = SUBSTRING(@Content, @Index, 1)
SET @TmpChar = N'' IF @Convert = 0
BEGIN
IF ISNUMERIC(@Part) = 0 OR @Part = N'.'
SET @TmpChar = @Part
ELSE
SELECT @TmpChar = [Char]
FROM [Private].[ChineseNum]
WHERE [Value] = @Part
END IF @Convert = 1
BEGIN
SET @TmpValue = -1 SELECT @TmpValue = [Value]
FROM [Private].[ChineseNum]
WHERE [Char] = @Part IF @TmpValue = -1 OR @TmpValue > 9
SET @TmpChar = @Part
ELSE
SET @TmpChar = @TmpValue END SET @Result = @Result + @TmpChar SET @Index = @Index + 1
END RETURN @Result
END

本帖最后由 lzmtw 于 2007-12-12 19:04:07 编辑

本帖最后由 lzmtw 于 2007-12-12 20:10:20 编辑

[Helper].[ChineseNumReader](下) IF @Convert = 1
BEGIN
--处理小数部分
DECLARE @Precision smallint /*保留原精度,含.*/
SET @Precision = LEN(@DecPart) IF @Precision = 0 SET @DecPart ='.' + @Zero + @Zero + @Zero
IF @Precision = 1 SET @DecPart = @DecPart + @Zero + @Zero + @Zero
IF @Precision = 2 SET @DecPart = @DecPart + @Zero + @Zero
IF @Precision = 3 SET @DecPart = @DecPart + @Zero DECLARE @TmpValue numeric(4, 3)
SET @DecPartValue = 0 SET @TmpValue = [Helper].[ChineseNum](SUBSTRING(@DecPart, 2, 1), 1)
SET @T1 = LEFT(@TmpValue, 1)
SET @DecPartValue = @DecPartValue + @TmpValue * 0.1 SET @TmpValue = [Helper].[ChineseNum](SUBSTRING(@DecPart, 3, 1) , 1)
SET @T2 = LEFT(@TmpValue, 1)
SET @DecPartValue =@DecPartValue + @TmpValue * 0.01 SET @TmpValue = [Helper].[ChineseNum](SUBSTRING(@DecPart, 4, 1) , 1)
SET @T3 = LEFT(@TmpValue, 1)
SET @DecPartValue = @DecPartValue + @TmpValue * 0.001 SET @Precision = @Precision - 1
IF @Precision = -1 SET @Precision = 0

IF @Precision = 0
SET @TValue = ''
ELSE
SET @TValue =SUBSTRING(CAST(@DecPartValue AS char(5)), 3, @Precision)
--以下处理整数位
DECLARE
@TmpInt bigint
,@TmpString nvarchar(31)
,@Count smallint
,@ValueString nvarchar(7)
SET @Count =4
SET @TmpString = @IntPart WHILE @Count > 0
BEGIN
IF @Count = 4 SET @Index = CHARINDEX(@3Char, @TmpString)
IF @Count = 3 SET @Index = CHARINDEX(@2Char, @TmpString)
IF @Count = 2 SET @Index = CHARINDEX(@1Char, @TmpString)
IF @Count = 1 SET @Index = LEN(@TmpString) SET @Part = LEFT(@TmpString, @Index)

IF LEN(@Part) > 0
SET @ValueString = LEFT(@Part, LEN(@Part) - 1)
ELSE
SET @ValueString ='' IF @Count = 4
SELECT
@33 = [3]
,@32 = [2]
,@31 = [1]
,@30 = [0]
,@3Value = [Value]
,@IsValid = Valid
FROM [Private].[GetLessTenThousandChineseNum](@ValueString, 1) IF @Count = 3
SELECT
@23 = [3]
,@22 = [2]
,@21 = [1]
,@20 = [0]
,@2Value = [Value]
,@IsValid = Valid
FROM [Private].[GetLessTenThousandChineseNum](@ValueString, 1) IF @Count = 2
SELECT
@13 = [3]
,@12 = [2]
,@11 = [1]
,@10 = [0]
,@1Value = [Value]
,@IsValid = Valid
FROM [Private].[GetLessTenThousandChineseNum](@ValueString, 1) IF @Count = 1
SELECT
@03 = [3]
,@02 = [2]
,@01 = [1]
,@00 = [0]
,@0Value = [Value]
,@IsValid = Valid
FROM [Private].[GetLessTenThousandChineseNum](@Part, 1)

IF @IsValid = 0 GOTO Finish SET @TmpString =REPLACE(@TmpString, @Part, N'') SET @Count = @Count -1
END SET @TmpInt = @3Value * POWER(@UnitTenthousandValue, 3) + @2Value * POWER(@UnitTenthousandValue, 2) + @1Value * POWER(@UnitTenthousandValue, 1) + @0Value SET @Value = @TmpInt IF LEN(@TValue) > 0
SET @Value = @Value + '.' + @TValue SET @Valid = 1
ENDFinish:
INSERT INTO @Table
VALUES
(
@33
,@32
,@31
,@30 ,@23
,@22
,@21
,@20 ,@13
,@12
,@11
,@10 ,@03
,@02
,@01
,@00 ,@T1
,@T2
,@T3 ,@Valid
,@Value ,@Input
)
RETURN
END

测试(还真的有些错,要改.不过下头的是正确的)
DECLARE @Value nvarchar(38)SET @Value = 9999999999999999.999
SELECT * FROM [LzmTWWorks].[Helper].[ChineseNumReader] (@Value, 0)SET @Value =N'玖仟玖佰玖拾玖兆玖仟玖佰玖拾玖亿玖仟玖佰玖拾玖万玖仟玖佰玖拾玖.玖玖玖'
SELECT * FROM [LzmTWWorks].[Helper].[ChineseNumReader] (@Value, 1)SET @Value = 9000009000009000
SELECT * FROM [LzmTWWorks].[Helper].[ChineseNumReader] (@Value, 0)SET @Value = N'玖仟兆零玖拾亿玖仟'
SELECT * FROM [LzmTWWorks].[Helper].[ChineseNumReader] (@Value, 1)/*
33   32   31   30   23   22   21   20   13   12   11   10   03   02   01   00   T1   T2   T3   Valid Value                                  Input
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- -------------------------------------- --------------------------------------
玖    玖    玖    玖    玖    玖    玖    玖    玖    玖    玖    玖    玖    玖    玖    玖    玖    玖    玖    1     玖仟玖佰玖拾玖兆玖仟玖佰玖拾玖亿玖仟玖佰玖拾玖万玖仟玖佰玖拾玖.玖玖玖    9999999999999999.999(1 行受影响)33   32   31   30   23   22   21   20   13   12   11   10   03   02   01   00   T1   T2   T3   Valid Value                                  Input
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- -------------------------------------- --------------------------------------
9    9    9    9    9    9    9    9    9    9    9    9    9    9    9    9    9    9    9    1     9999999999999999.999                   玖仟玖佰玖拾玖兆玖仟玖佰玖拾玖亿玖仟玖佰玖拾玖万玖仟玖佰玖拾玖.玖玖玖(1 行受影响)33   32   31   30   23   22   21   20   13   12   11   10   03   02   01   00   T1   T2   T3   Valid Value                                  Input
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- -------------------------------------- --------------------------------------
玖    零    零    零    零    零    玖    零    零    零    零    零    玖    零    零    零    零    零    零    1     玖仟兆零玖拾亿玖仟                              9000009000009000.000(1 行受影响)33   32   31   30   23   22   21   20   13   12   11   10   03   02   01   00   T1   T2   T3   Valid Value                                  Input
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- -------------------------------------- --------------------------------------
9    0    0    0    0    0    9    0    0    0    0    0    9    0    0    0    0    0    0    1     9000009000009000                       玖仟兆零玖拾亿玖仟(1 行受影响)*/

修改了自己发现出错的地方,非常低级也非常关键    SET @Index = CHARINDEX(N'.', @Input)
    IF @Index > 0
        BEGIN
            SET @IntPart = LEFT(@Input, @Index - 1)
            SET @DecPart = RIGHT(@Input, LEN(@Input) - @Index + 1)
        END
    ELSE
        BEGIN
            SET @IntPart = @Input
            SET @DecPart = N''
        END原来居然是    SET @Index = CHARINDEX(N'.', @Input)
    IF @Index > 0
        BEGIN
            SET @IntPart = LEFT(@Input, @Index)
            SET @DecPart = RIGHT(@Input, LEN(@Input) - @Index + 1)
        END
    ELSE
        BEGIN
            SET @IntPart = @Input
            SET @DecPart = N''
        END函数调试也太辛苦了.变量一多,更是顾上头顾不上尾了,连变量在哪声明怎么声明都要泛糊涂的了.

日期与中国大写日期的相互转换[Helper].[ChineseDate]
USE [LzmTWWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Helper].[ChineseDate]
(
@Date nvarchar(14)
,@Convert bit = 0
)
RETURNS
@Table TABLE
(
Input nvarchar(14)
,[Year] nvarchar(4)
,[Month] nvarchar(3)
,[Day] nvarchar(4)
,[Value] nvarchar(14)
,Valid bit
)
AS
BEGIN
DECLARE
@Input nvarchar(14)
,@Year nvarchar(4)
,@Month nvarchar(4)
,@Day nvarchar(3)
,@Value nvarchar(14)
,@Valid bit
,@IsValid bit SET @Input = @Date
SET @Valid = 0
SET @IsValid = 1 IF (@Convert = 0 AND ISDATE(@Input) = 0)
SET @IsValid = 0 IF (@Convert = 1)
BEGIN
IF CHARINDEX(N'年' , @Input) = 0
OR CHARINDEX(N'月' , @Input) = 0
OR CHARINDEX(N'日' , @Input) = 0
SET @IsValid = 0
END IF @IsValid = 0 GOTO Finish IF @Convert = 0
BEGIN
DECLARE
@TmpDate smalldatetime
,@Zero nchar(1) SET @TmpDate = @Input SELECT @Zero = [Char]
FROM [Private].[ChineseNum]
WHERE [Value] = 0 DECLARE
@YearValue smallint
,@MonthValue tinyint
,@DayValue tinyint SET @YearValue = YEAR(@TmpDate)
SET @MonthValue = MONTH(@TmpDate)
SET @DayValue = DAY(@TmpDate) SET @Year = [Helper].[ChineseNum](@YearValue, 0) SELECT
@Month = [Value]
,@IsValid= [Valid]
FROM [Private].[GetLessTenThousandChineseNum](@MonthValue, 0)

IF @IsValid = 0 GOTO Finish SELECT
@Day = [Value]
,@IsValid= [Valid]
FROM [Private].[GetLessTenThousandChineseNum](@DayValue, 0) IF @IsValid = 0 GOTO Finish IF @MonthValue IN (1, 2) /*这里添加月的约定*/
SET @Month = @Zero + @Month IF @DayValue IN (1, 2, 3, 4, 5, 6, 7, 8, 9) /*这里添加日的约定*/
SET @Day = @Zero + @Day SET @Value = @Year+ N'年' + @Month+ N'月' + @Day + N'日'
SET @Valid = 1
END IF @Convert = 1
BEGIN
DECLARE
@TmpValue smalldatetime
,@TmpString nvarchar(14)
,@Part nvarchar(5)
,@Index smallint SET @TmpString = @Input SET @Index = CHARINDEX(N'年', @TmpString)
SET @Part = LEFT(@TmpString, @Index)
SET @Year = LEFT(@Part,  LEN(@Part) - 1)
SET @Year = [Helper].[ChineseNum](@Year, 1)
SET @TmpString =REPLACE(@TmpString, @Part, N'') SET @Index = CHARINDEX(N'月', @TmpString)
SET @Part = LEFT(@TmpString, @Index)
SET @Month = LEFT(@Part,  LEN(@Part) - 1)
SELECT
@Month = [Value]
,@IsValid= [Valid]
FROM [Private].[GetLessTenThousandChineseNum](@Month, 1)
SET @TmpString =REPLACE(@TmpString, @Part, N'') IF @IsValid = 0 GOTO Finish SET @Index = CHARINDEX(N'日', @TmpString)
SET @Part = LEFT(@TmpString, @Index)
SET @Day = LEFT(@Part,  LEN(@Part) - 1)
SELECT
@Day = [Value]
,@IsValid= [Valid]
FROM [Private].[GetLessTenThousandChineseNum](@Day, 1) IF @IsValid = 0 GOTO Finish SET @TmpValue =@Year + '-' + @Month + '-' + @Day SET @Value = CONVERT(nvarchar(10), @TmpValue, 20)
SET @Valid = 1
ENDFinish: IF @IsValid = 0
BEGIN
SET @Year = NULL
SET @Month = NULL
SET @Day = NULL
END INSERT INTO @Table
VALUES(@Input, @Year, @Month, @Day, @Value, @Valid)
RETURN
END

示例
SET NOCOUNT ON
DECLARE @Value nvarchar(14)SET @Value = '20070121'
SELECT * FROM [LzmTWWorks].[Helper].[ChineseDate] (@Value ,0)SET @Value =N'贰零零柒年零壹月贰拾壹日'
SELECT * FROM [LzmTWWorks].[Helper].[ChineseDate] (@Value ,1)/*
Input          Year Month Day  Value          Valid
-------------- ---- ----- ---- -------------- -----
20070121       贰零零柒零壹    贰拾壹  贰零零柒年零壹月贰拾壹日   1Input          Year Month Day  Value          Valid
-------------- ---- ----- ---- -------------- -----
贰零零柒年零壹月贰拾壹日   2007 1     21   2007-01-21     1
*/

人民币数字与大写的相互转换[Helper].[ChineseRMB]
USE [LzmTWWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Helper].[ChineseRMB]
(
@Content nvarchar(41)
,@Convert bit = 0
)
RETURNS
@Table TABLE
(
[33] nvarchar(1)
,[32] nvarchar(1)
,[31] nvarchar(1)
,[30] nvarchar(1) ,[23] nvarchar(1)
,[22] nvarchar(1)
,[21] nvarchar(1)
,[20] nvarchar(1) ,[13] nvarchar(1)
,[12] nvarchar(1)
,[11] nvarchar(1)
,[10] nvarchar(1) ,[03] nvarchar(1)
,[02] nvarchar(1)
,[01] nvarchar(1)
,[00] nvarchar(1) ,T1 nvarchar(1)
,T2 nvarchar(1)
,T3 nvarchar(1) ,Valid bit DEFAULT(0)
,[Value] nvarchar(41) ,Input nvarchar(41)
)
AS
BEGIN
DECLARE
@Input nvarchar(41)
,@IsValid bit
,@NumContent nvarchar(41) SET @IsValid = 0 SET @Input = @Content
IF @Input IS NULL GOTO Error SET @Input =LTRIM(RTRIM(@Input)) IF LEN(@Input) = 0 GOTO ERROR SET @IsValid = 1 DECLARE
@Prefix0 nchar(1)
,@Prefix1 nchar(3)
,@Unit nchar(1)
,@Point nchar(1)
,@Suffix0 nchar(1)
,@Suffix1 nchar(1)
,@Jiao nchar(1)
,@Fen nchar(1)
,@Hao nchar(1)
,@Comma nchar(1) SET @Prefix0 = N'￥'
SET @Prefix1 = N'人民币'
SET @Unit = N'元'
SET @Point = N'.'
SET @Suffix0 = N'整'
SET @Suffix1 = N'正'
SET @Jiao = N'角'
SET @Fen = N'分'
SET @Hao = N'毫'
SET @Comma = N',' DECLARE
@Zero nchar(1)
,@Index tinyint
,@Value nvarchar(41)
,@IntPart nvarchar(34)
,@DecPart nvarchar(7)
,@PointIndex tinyint
,@Length tinyint SET @IntPart = N''
SET @DecPart = N'' SELECT @Zero = [Char]
FROM [Private].[ChineseNum]
WHERE [UnitType] = 'Zero' IF @Convert = 0
BEGIN
SET @NumContent = REPLACE(@Input, @Prefix0, N'')
SET @NumContent = REPLACE(@NumContent, @Comma, N'') INSERT INTO @Table
SELECT * FROM [Helper].[ChineseNumReader](@NumContent, @Convert) SELECT
@IsValid = Valid
,@Value = [Value]
FROM @Table UPDATE @Table SET Input = @Input IF @IsValid = 1
BEGIN
SET @PointIndex = CHARINDEX(@Point, @Value) IF @PointIndex = 0
SET @IntPart = @Value --处理角分毫部分
IF @PointIndex > 0
BEGIN
SET @IntPart = LEFT(@Value, @PointIndex - 1)
SET @DecPart = RIGHT(@Value, LEN(@Value) - @PointIndex + 1)
SET @Length = LEN(@DecPart) IF @Length = 4
IF CHARINDEX(@Zero + @Zero, @DecPart) > 0
SET @DecPart = REPLACE(@DecPart, @Zero + @Zero, @Zero) + @Hao
ELSE
IF SUBSTRING(@DecPart, 2, 1) = @Zero
SET @DecPart = STUFF(@DecPart, 4, 0, @Fen) + @Hao
ELSE
IF SUBSTRING(@DecPart, 3, 1) = @Zero
SET @DecPart = STUFF(@DecPart, 3, 0, @Jiao) + @Hao
Else
BEGIN
SET @DecPart =  STUFF(@DecPart, 4, 0, @Fen) + @Hao
SET @DecPart =  STUFF(@DecPart, 3, 0, @Jiao)
END IF @Length = 3
IF SUBSTRING(@DecPart, 2, 1) = @Zero
SET @DecPart = @DecPart + @Fen
Else
BEGIN
SET @DecPart =  @DecPart + @Fen
SET @DecPart =  STUFF(@DecPart, 3, 0, @Jiao)
END IF @Length = 2
SET @DecPart = @DecPart + @Jiao

SET @DecPart =  REPLACE(@DecPart, @Point, @Unit)
END --处理整元部分
IF LEN(@IntPart) = 0 SET @IntPart = @Zero IF LEN(@DecPart) = 0
SET @IntPart = @IntPart + @Unit + @Suffix0 SET @IntPart = @Prefix1 + @IntPart --综合两部分并更新
SET @Value = @IntPart + @DecPart
UPDATE @Table SET [Value] = @Value
END IF @IsValid = 0 SET @IsValid = 1 /*忽略此错误*/
END IF @Convert = 1
BEGIN
SET @NumContent = REPLACE(@Input, @Prefix1, N'')
SET @NumContent = REPLACE(@NumContent, @Suffix0, N'')
SET @NumContent = REPLACE(@NumContent, @Suffix1, N'')
SET @NumContent = REPLACE(@NumContent, @Unit, @Point) SET @PointIndex = CHARINDEX(@Point, @NumContent) IF @PointIndex = 0
SET @IntPart = @NumContent IF @PointIndex > 0
BEGIN
SET @IntPart = LEFT(@NumContent, @PointIndex - 1)
SET @DecPart = RIGHT(@NumContent, LEN(@NumContent) - @PointIndex + 1)
SET @Length = LEN(@DecPart) IF @Length = 4 AND RIGHT(@DecPart, 1) = @Hao /*处理元零?毫*/
SET @DecPart = REPLACE(@DecPart, @Zero, @Zero + @Zero) SET @DecPart = REPLACE(@DecPart, @Jiao, N'')
SET @DecPart = REPLACE(@DecPart, @Fen, N'')
SET @DecPart = REPLACE(@DecPart, @Hao, N'')
END SET @Value = @IntPart + @DecPart INSERT INTO @Table
SELECT * FROM [Helper].[ChineseNumReader](@Value, @Convert) UPDATE @Table SET Input = @Input SELECT
@IsValid = Valid
,@Value = [Value]
FROM @Table IF @IsValid = 1
BEGIN
SET @PointIndex = CHARINDEX(@Point, @Value) IF @PointIndex = 0
BEGIN
SET @IntPart = @Value
SET @DecPart = N'.00' /*保证至分位的小数位*/
END IF @PointIndex > 0
BEGIN
SET @IntPart = LEFT(@Value, @PointIndex - 1)
SET @DecPart = RIGHT(@Value, LEN(@Value) - @PointIndex + 1)
SET @Length = LEN(@DecPart) IF @Length = 2 SET @DecPart = @DecPart + N'0' /*保证分位的小数位*/
END --处理分号。整数位最多有16位。
SET @Length = LEN(@IntPart) IF @Length > 3
BEGIN
DECLARE
@Count tinyint
,@Counter tinyint SET @Count = @Length / 3
IF @Length % 3 = 0
SET @Count = @Count - 1 SET @Index = @Count
SET @Counter = 1 WHILE @Index > 0
BEGIN
SET @IntPart = STUFF(@Intpart,@Length - @Index * 3 + @Counter, 0, @Comma)
SET @Counter = @Counter + 1
SET @Index = @Index - 1
END
END

SET @IntPart = @Prefix0 + @IntPart
SET @Value = @IntPart + @DecPart UPDATE @Table SET [Value] = @Value
END IF @IsValid = 0 SET @IsValid = 1 /*忽略此错误*/
END Error:
IF @IsValid = 0
INSERT INTO @Table(Input) VALUES(@Input)
RETURN
END

本帖最后由 lzmtw 于 2007-12-13 22:39:34 编辑

SET NOCOUNT ON
DECLARE @Input nvarchar(38)SET @Input = N'12345678009.008'PRINT N'
我这样做的好处现在出来了。
在EXCEL的单元格中，对数字有两种特殊格式可设置，一是中文大写格式，一是中文小写格式。
如' + @Input + N'
中文大写格式是：壹佰贰拾叁亿肆仟伍佰陆拾柒万捌仟零玖.零零捌
中文小写格式是：一百二十三亿四千五百六十七万八千○九.○○八现在我可这样做了，结果是：'DECLARE @Value nvarchar(38)
SELECT @Value = [Value] FROM [LzmTWWorks].[Helper].[ChineseNumReader] (@Input, 0 )PRINT N'
中文大写格式是：' + @ValueUPDATE [Private].[ChineseNum] SET [Char] = N'○' WHERE [Char] = N'零'
UPDATE [Private].[ChineseNum] SET [Char] = N'一' WHERE [Char] = N'壹'
UPDATE [Private].[ChineseNum] SET [Char] = N'二' WHERE [Char] = N'贰'
UPDATE [Private].[ChineseNum] SET [Char] = N'三' WHERE [Char] = N'叁'
UPDATE [Private].[ChineseNum] SET [Char] = N'四' WHERE [Char] = N'肆'
UPDATE [Private].[ChineseNum] SET [Char] = N'五' WHERE [Char] = N'伍'
UPDATE [Private].[ChineseNum] SET [Char] = N'六' WHERE [Char] = N'陆'
UPDATE [Private].[ChineseNum] SET [Char] = N'七' WHERE [Char] = N'柒'
UPDATE [Private].[ChineseNum] SET [Char] = N'八' WHERE [Char] = N'捌'
UPDATE [Private].[ChineseNum] SET [Char] = N'九' WHERE [Char] = N'玖'
UPDATE [Private].[ChineseNum] SET [Char] = N'十' WHERE [Char] = N'拾'
UPDATE [Private].[ChineseNum] SET [Char] = N'百' WHERE [Char] = N'佰'
UPDATE [Private].[ChineseNum] SET [Char] = N'千' WHERE [Char] = N'仟'
UPDATE [Private].[ChineseNum] SET [Char] = N'万' WHERE [Char] = N'万'
UPDATE [Private].[ChineseNum] SET [Char] = N'亿' WHERE [Char] = N'亿'
UPDATE [Private].[ChineseNum] SET [Char] = N'兆' WHERE [Char] = N'兆'SELECT @Value = [Value] FROM [LzmTWWorks].[Helper].[ChineseNumReader] (@Input, 0 )PRINT N'
中文小写格式是：' + @ValueUPDATE [Private].[ChineseNum] SET [Char] = N'零' WHERE [Char] = N'○'
UPDATE [Private].[ChineseNum] SET [Char] = N'壹' WHERE [Char] = N'一'
UPDATE [Private].[ChineseNum] SET [Char] = N'贰' WHERE [Char] = N'二'
UPDATE [Private].[ChineseNum] SET [Char] = N'叁' WHERE [Char] = N'三'
UPDATE [Private].[ChineseNum] SET [Char] = N'肆' WHERE [Char] = N'四'
UPDATE [Private].[ChineseNum] SET [Char] = N'伍' WHERE [Char] = N'五'
UPDATE [Private].[ChineseNum] SET [Char] = N'陆' WHERE [Char] = N'六'
UPDATE [Private].[ChineseNum] SET [Char] = N'柒' WHERE [Char] = N'七'
UPDATE [Private].[ChineseNum] SET [Char] = N'捌' WHERE [Char] = N'八'
UPDATE [Private].[ChineseNum] SET [Char] = N'玖' WHERE [Char] = N'九'
UPDATE [Private].[ChineseNum] SET [Char] = N'拾' WHERE [Char] = N'十'
UPDATE [Private].[ChineseNum] SET [Char] = N'佰' WHERE [Char] = N'百'
UPDATE [Private].[ChineseNum] SET [Char] = N'仟' WHERE [Char] = N'千'
UPDATE [Private].[ChineseNum] SET [Char] = N'万' WHERE [Char] = N'万'
UPDATE [Private].[ChineseNum] SET [Char] = N'亿' WHERE [Char] = N'亿'
UPDATE [Private].[ChineseNum] SET [Char] = N'兆' WHERE [Char] = N'兆'/*我这样做的好处现在出来了。
在EXCEL的单元格中，对数字有两种特殊格式可设置，一是中文大写格式，一是中文小写格式。
如12345678009.008
中文大写格式是：壹佰贰拾叁亿肆仟伍佰陆拾柒万捌仟零玖.零零捌
中文小写格式是：一百二十三亿四千五百六十七万八千○九.○○八现在我可这样做了，结果是：中文大写格式是：壹佰贰拾叁亿肆仟伍佰陆拾柒万捌仟零玖.零零捌中文小写格式是：一百二十三亿四千五百六十七万八千○九.○○八*/

另外，写Update,Insert之类的多语句，最好呢，自己做一个正则应用程序。
我的做得简陋，但是经常用到。

那个小工具自己做的。里头重载了.NET里的Regex类，有一些特殊的用法。
没有上传到资源。可以自己动手做一个的。

都是些处理数字的中文函数.不大常用,但在一些特别的地方会用到.我说了,是有钱人才经常用到.在填写支票的时候，或签定重要合同的时候.包括壹式叁份之类的。写这些时,我google了一下,看看跟钱有关系的数字有何需求,就写了上面的函数.但现在为止,我还没用过ChineseDate.什么“贰零零柒年零壹月贰拾壹日”的

调试易

中国数字(欢迎验错)

解决方案 »