16,552
社区成员
发帖
与我相关
我的任务
分享数字读法的互转与数字人民币读法的互转(SQLSERVER2005)
应用环境:SqlServer2005及以上
没正式使用过.仅供参考.
代码为原始版本,没整理过.
没正式使用过.仅供参考.
代码为原始版本,没整理过.
...全文
请发表友善的回复…
发表回复
HanMo 2008-12-04
- 打赏
- 举报
自帐号,结贴
水如烟 2008-11-21
- 打赏
- 举报
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 [Chinese].[Num]
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完
水如烟 2008-11-21
- 打赏
- 举报
续上
--以下处理格式
SET @Value =''
DECLARE @Tmp nvarchar(8)
IF LEN(@3Value) > 0
BEGIN
SET @Tmp = @3Value + @3Char
SET @Value = @Value + @Tmp
END
IF LEN(@2Value) > 0
BEGIN
SET @Tmp = @2Value + @2Char
IF @23 = @Zero AND LEN(@Value) > 0
SET @Tmp = @Zero + @Tmp
SET @Value = @Value + @Tmp
END
IF LEN(@1Value) > 0
BEGIN
SET @Tmp = @1Value + @1Char
IF @13 = @Zero AND LEN(@Value) > 0
SET @Tmp = @Zero + @Tmp
SET @Value = @Value + @Tmp
END
IF LEN(@0Value) > 0
BEGIN
SET @Tmp = @0Value
IF @03 = @Zero AND LEN(@Value) > 0
SET @Tmp = @Zero + @Tmp
SET @Value = @Value + @Tmp
END
IF LEN(@Value) = 0
SET @Value = @Zero
IF LEN(@TValue) > 0
SET @Value = @Value + '.' + @TValue
SET @Valid = 1
END
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 [Chinese].[GetLessTenThousandNum](@ValueString, 1)
IF @Count = 3
SELECT
@23 = [3]
,@22 = [2]
,@21 = [1]
,@20 = [0]
,@2Value = [Value]
,@IsValid = Valid
FROM [Chinese].[GetLessTenThousandNum](@ValueString, 1)
IF @Count = 2
SELECT
@13 = [3]
,@12 = [2]
,@11 = [1]
,@10 = [0]
,@1Value = [Value]
,@IsValid = Valid
FROM [Chinese].[GetLessTenThousandNum](@ValueString, 1)
IF @Count = 1
SELECT
@03 = [3]
,@02 = [2]
,@01 = [1]
,@00 = [0]
,@0Value = [Value]
,@IsValid = Valid
FROM [Chinese].[GetLessTenThousandNum](@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
END
Finish:
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水如烟 2008-11-21
- 打赏
- 举报
CREATE FUNCTION [Helper].[ChineseNumReader]
(
@Content nvarchar(38)
,@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(38)
,Input nvarchar(38)
)
AS
BEGIN
DECLARE
@Input nvarchar(38)
,@33 nvarchar(1)
,@32 nvarchar(1)
,@31 nvarchar(1)
,@30 nvarchar(1)
,@3Value nvarchar(7)
,@23 nvarchar(1)
,@22 nvarchar(1)
,@21 nvarchar(1)
,@20 nvarchar(1)
,@2Value nvarchar(7)
,@13 nvarchar(1)
,@12 nvarchar(1)
,@11 nvarchar(1)
,@10 nvarchar(1)
,@1Value nvarchar(7)
,@03 nvarchar(1)
,@02 nvarchar(1)
,@01 nvarchar(1)
,@00 nvarchar(1)
,@0Value nvarchar(7)
,@T1 nvarchar(1)
,@T2 nvarchar(1)
,@T3 nvarchar(1)
,@TValue nvarchar(7)
,@TValid bit
,@Value nvarchar(38)
,@Valid bit
DECLARE
@IsValid bit
,@Index smallint
SET @Input = @Content
SET @Valid = 0
SET @IsValid = 1
IF @Input Is NULL GOTO Finish
IF LEN(@Input) = 0 GOTO Finish
IF @Convert = 0
BEGIN
IF ISNUMERIC(@Input) = 0 GOTO Finish
SET @Input =CAST(@Input AS numeric(19, 3)) /*小数第四位四舍五入*/
END
DECLARE
@IntPart nvarchar(31)
,@DecPart nvarchar(7)
,@IntPartValue numeric(16, 0)
,@DecPartValue numeric(4,3)
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
DECLARE
@Zero nchar(1)
,@3Char nchar(1)
,@2Char nchar(1)
,@1Char nchar(1)
,@UnitTenthousandValue numeric(5,0)
,@SingleChar nchar(1)
,@Part nvarchar(38)
--取中文单位零，兆，亿，万
SELECT @Zero = [Char]
FROM [Chinese].[Num]
WHERE [UnitType] = 'Zero'
SELECT @UnitTenthousandValue = [Value]
FROM [Chinese].[Num]
WHERE [UnitType] = 'UnitTenthousand'
SET @Index = 3
WHILE @Index > 0
BEGIN
SELECT @SingleChar = [Char]
FROM [Chinese].[Num]
WHERE [Value] = POWER(@UnitTenthousandValue, @Index)
IF @Index = 3 SET @3Char = @SingleChar
IF @Index = 2 SET @2Char = @SingleChar
IF @Index = 1 SET @1Char = @SingleChar
SET @Index = @Index - 1
END
IF @Convert = 0
BEGIN
IF LEN(@DecPart) = 0
SET @DecPart = N'0.0'
ELSE
SET @DecPart = @DecPart + N'0' /*保证小数点后面有位数*/
SET @IntPartValue = @IntPart /*重新初始变量和字串*/
SET @DecPartValue = @DecPart
SET @IntPart = @IntPartValue
SET @DecPart = @DecPartValue
--以下处理小数部分，这部分直读。小数部分总是0.???格式
SET @T1 = [Helper].ChineseNum(SUBSTRING(@DecPart, 3, 1), 0)
SET @T2 = [Helper].ChineseNum(SUBSTRING(@DecPart, 4, 1), 0)
SET @T3 = [Helper].ChineseNum(SUBSTRING(@DecPart, 5, 1), 0)
IF @T3 <> @Zero
SET @TValue = @T1 + @T2 + @T3
ELSE
IF @T2 <> @Zero
SET @TValue = @T1 + @T2
ELSE
IF @T1 <> @Zero
SET @TValue = @T1
ELSE
SET @TValue = ''
SET @TValid = 1
--以下处理整数位。从个位开始，每次截取四位处理(即以万为单位)。
DECLARE @Length smallint
SET @Index = 0
SET @Length = LEN(@IntPart)
WHILE @Index < 4
BEGIN
SET @Part = SUBSTRING(@IntPart,@Length - (@Index + 1) * 4 + 1, 4)
IF LEN(@Part) = 0 SET @PART = '0'
IF @Index = 0
SELECT
@03 = [3]
,@02 = [2]
,@01 = [1]
,@00 = [0]
,@0Value =[Value]
,@IsValid =Valid
FROM [Chinese].[GetLessTenThousandNum](@Part, 0)
IF @Index = 1
SELECT
@13 = [3]
,@12 = [2]
,@11 = [1]
,@10 = [0]
,@1Value =[Value]
,@IsValid =Valid
FROM [Chinese].[GetLessTenThousandNum](@Part, 0)
IF @Index = 2
SELECT
@23 = [3]
,@22 = [2]
,@21 = [1]
,@20 = [0]
,@2Value =[Value]
,@IsValid =Valid
FROM [Chinese].[GetLessTenThousandNum](@Part, 0)
IF @Index = 3
SELECT
@33 = [3]
,@32 = [2]
,@31 = [1]
,@30 = [0]
,@3Value =[Value]
,@IsValid =Valid
FROM [Chinese].[GetLessTenThousandNum](@Part, 0)
IF @IsValid = 0 GOTO Finish
SET @Index = @Index + 1
END水如烟 2008-11-21
- 打赏
- 举报
CREATE FUNCTION [Chinese].[GetLessTenThousandNum]
(
@LessTenThousandValue nvarchar(7)
,@Convert bit = 0
)
RETURNS
@Table TABLE
(
Input nvarchar(7)
,[3] nvarchar(1)
,[2] nvarchar(1)
,[1] nvarchar(1)
,[0] nvarchar(1)
,[Value] nvarchar(7)
,Valid bit
)
AS
BEGIN
DECLARE
@Input nvarchar(7)
,@3 nvarchar(2)
,@2 nvarchar(3)
,@1 nvarchar(3)
,@0 nvarchar(2)
,@Value nvarchar(7)
,@Valid bit
DECLARE
@IsValid bit /*检验输入达式是否有效*/
SET @Input = @LessTenThousandValue
SET @Value = N''
--检验输入达式是否有效,开始
SET @IsValid = 1
IF (@Convert = 0 AND ISNUMERIC(@Input) = 0)
SET @IsValid = 0
IF (@Convert = 1)
BEGIN
DECLARE
@TmpIndex tinyint
,@Char nchar(1)
SET @TmpIndex = LEN(@Input)
WHILE @TmpIndex > 0
BEGIN
SET @Char = SUBSTRING(@Input, @TmpIndex, 1)
IF NOT EXISTS
(
SELECT *
FROM [Chinese].[Num]
WHERE [Char] = @Char
)
BEGIN
SET @IsValid = 0
BREAK
END
SET @TmpIndex = @TmpIndex - 1
END
END
IF @IsValid = 0
BEGIN
INSERT INTO @Table
VALUES(@Input, @3, @2, @1, @0, @Value, @Valid)
RETURN
END /*检验输入达式结束*/
DECLARE
@Zero nchar(1)
,@3Char nchar(1)
,@2Char nchar(1)
,@1Char nchar(1)
,@Index smallint
,@UnitThousandValue smallint
,@SingleChar nchar(1)
--取中文单位零，仟，佰，拾
SELECT @Zero = [Char]
FROM [Chinese].[Num]
WHERE [UnitType] = 'Zero'
SELECT @UnitThousandValue = [Value]
FROM [Chinese].[Num]
WHERE [UnitType] = 'UnitThousand'
SET @Index = 3
WHILE @Index > 0
BEGIN
SELECT @SingleChar = [Char]
FROM [Chinese].[Num]
WHERE [Value] = POWER(@UnitThousandValue, @Index)
IF @Index = 3 SET @3Char = @SingleChar
IF @Index = 2 SET @2Char = @SingleChar
IF @Index = 1 SET @1Char = @SingleChar
SET @Index = @Index - 1
END
IF @Convert = 0
BEGIN
DECLARE
@OperateValue smallint
,@Divisor smallint
,@Quotient smallint
,@Remainder smallint
SET @OperateValue = @Input
SET @Divisor = POWER(@UnitThousandValue, 3)
SET @Index = 3
WHILE @Index > -1
BEGIN
SET @Quotient = @OperateValue / @Divisor
SET @Remainder = @OperateValue % @Divisor
IF @Index = 3 SET @3 = [Helper].[ChineseNum](@Quotient, 0)
IF @Index = 2 SET @2 = [Helper].[ChineseNum](@Quotient, 0)
IF @Index = 1 SET @1 = [Helper].[ChineseNum](@Quotient, 0)
IF @Index = 0 SET @0 = [Helper].[ChineseNum](@Quotient, 0)
SET @Divisor = @Divisor / @UnitThousandValue
SET @OperateValue = @Remainder
SET @Index = @Index - 1
END
SET @Valid = 1
INSERT INTO @Table
VALUES(@Input, @3, @2, @1, @0, @Value, @Valid)
IF @3 = @Zero SET @3 = N'' ELSE SET @3 = @3 + @3Char
IF @2 = @Zero SET @2 = N'' ELSE SET @2 = @2 + @2Char
IF @1 = @Zero SET @1 = N'' ELSE SET @1 = @1 + @1Char
IF @0 = @Zero SET @0 = N''
IF LEN(@3) > 0
IF LEN(@2) = 0
BEGIN
IF LEN(@1) > 0
SET @1 =@Zero + @1
ELSE
IF LEN(@0) > 0
SET @0 =@Zero + @0
END
ELSE
BEGIN
IF LEN(@1) = 0
IF LEN(@0) > 0
SET @0 =@Zero + @0
END
ELSE
BEGIN
IF LEN(@2) > 0
IF LEN(@1) = 0 AND LEN(@0) > 0
SET @0 =@Zero + @0
END
SET @Value = @3 + @2 + @1 + @0
UPDATE @Table
SET [Value] = @Value
END
IF @Convert = 1
BEGIN
SET @3 = N'0'
SET @2 = N'0'
SET @1 = N'0'
SET @0 = N'0'
DECLARE
@TmpValue smallint
,@TmpString nvarchar(7)
,@Part nvarchar(3)
,@ValuePart nvarchar(1)
,@Count tinyint
SET @Count =4
SET @TmpString = @Input
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)
IF @Index > 0
BEGIN
SET @Part = LEFT(@TmpString, @Index)
IF @Count > 1
SET @ValuePart = SUBSTRING(@Part, LEN(@Part) - 1, 1)
ELSE
SET @ValuePart = SUBSTRING(@Part, LEN(@Part), 1)
SELECT @SingleChar = [Helper].[ChineseNum](@ValuePart, 1)
IF @Count = 4 SET @3 = @SingleChar
IF @Count = 3 SET @2 = @SingleChar
IF @Count = 2 SET @1 = @SingleChar
IF @Count = 1 SET @0 = @SingleChar
SET @TmpString =REPLACE(@TmpString, @Part, N'')
END
SET @Count = @Count -1
END
SET @TmpValue = @3 * 1000 + @2 * 100 + @1 * 10 + @0
SET @Value = @TmpValue
SET @Valid = 1
INSERT INTO @Table
VALUES(@Input, @3, @2, @1, @0, @Value, @Valid)
END
RETURN
END水如烟 2008-11-21
- 打赏
- 举报
读数字函数
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 [Chinese].[Num]
WHERE [Value] = @Part
END
IF @Convert = 1
BEGIN
SET @TmpValue = -1
SELECT @TmpValue = [Value]
FROM [Chinese].[Num]
WHERE [Char] = @Part
IF @TmpValue = -1 OR @TmpValue > 9
SET @TmpChar = @Part
ELSE
SET @TmpChar = @TmpValue
END
IF @Convert IS NULL
SELECT @TmpChar = [Simple]
FROM [Chinese].[Num]
WHERE [Value] = @Part
SET @Result = @Result + @TmpChar
SET @Index = @Index + 1
END
RETURN @Result
END水如烟 2008-11-21
- 打赏
- 举报
先建命名空间Chinese,Helper
建一个信息表
导入数据
建一个信息表
CREATE TABLE [Chinese].[Num](
[Char] [nchar](1) COLLATE Chinese_PRC_CI_AS NULL,
[Value] [bigint] NOT NULL,
[UnitType] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Simple] [nchar](1) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_Num] PRIMARY KEY CLUSTERED
(
[Value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]导入数据
INSERT INTO [LzmtwWorks].[Chinese].[Num]
([Char]
,[Value]
,[UnitType]
,[Simple])
SELECT '零',0,'Zero','零' UNION ALL
SELECT '壹',1,NULL,'一' UNION ALL
SELECT '贰',2,NULL,'二' UNION ALL
SELECT '叁',3,NULL,'三' UNION ALL
SELECT '肆',4,NULL,'四' UNION ALL
SELECT '伍',5,NULL,'五' UNION ALL
SELECT '陆',6,NULL,'六' UNION ALL
SELECT '柒',7,NULL,'七' UNION ALL
SELECT '捌',8,NULL,'八' UNION ALL
SELECT '玖',9,NULL,'九' UNION ALL
SELECT '拾',10,'UnitThousand','十' UNION ALL
SELECT '佰',100,NULL,'百' UNION ALL
SELECT '仟',1000,NULL,'千' UNION ALL
SELECT '万',10000,'UnitTenthousand','万' UNION ALL
SELECT '亿',100000000,NULL,'亿' UNION ALL
SELECT '兆',1000000000000,NULL,'兆' 水如烟 2008-11-21
- 打赏
- 举报
SELECT * FROM [LzmtwWorks].[Helper].[ChineseRMB] (
'人民币壹仟兆柒仟捌佰亿零陆万玖仟元肆角零柒毫'
,1)33 32 31 30 23 22 21 20 13 12 11 10 03 02 01 00 T1 T2 T3 Valid Value Input
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ----------------------------------------- -----------------------------------------
1 0 0 0 7 8 0 0 0 0 0 6 9 0 0 0 4 0 7 1 ￥1,000,780,000,069,000.407 人民币壹仟兆柒仟捌佰亿零陆万玖仟元肆角零柒毫
(1 行受影响)SELECT * FROM [LzmtwWorks].[Helper].[ChineseRMB] (
'壹仟兆柒仟捌佰亿零陆万玖仟元肆角零柒毫'
,1)33 32 31 30 23 22 21 20 13 12 11 10 03 02 01 00 T1 T2 T3 Valid Value Input
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ----------------------------------------- -----------------------------------------
1 0 0 0 7 8 0 0 0 0 0 6 9 0 0 0 4 0 7 1 ￥1,000,780,000,069,000.407 壹仟兆柒仟捌佰亿零陆万玖仟元肆角零柒毫
(1 行受影响)
水如烟 2008-11-21
- 打赏
- 举报
SELECT * FROM [LzmtwWorks].[Helper].[ChineseRMB] (
'￥1,000,780,000,069,000.407'
,0)33 32 31 30 23 22 21 20 13 12 11 10 03 02 01 00 T1 T2 T3 Valid Value Input
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ----------------------------------------- -----------------------------------------
壹 零 零 零 柒 捌 零 零 零 零 零 陆 玖 零 零 零 肆 零 柒 1 人民币壹仟兆柒仟捌佰亿零陆万玖仟元肆角零柒毫 ￥1,000,780,000,069,000.407
(1 行受影响)
水如烟 2008-11-21
- 打赏
- 举报
SELECT * FROM [LzmtwWorks].[Helper].[ChineseRMB] (
'1000780000069000.407'
,0)33 32 31 30 23 22 21 20 13 12 11 10 03 02 01 00 T1 T2 T3 Valid Value Input
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ----------------------------------------- -----------------------------------------
壹 零 零 零 柒 捌 零 零 零 零 零 陆 玖 零 零 零 肆 零 柒 1 人民币壹仟兆柒仟捌佰亿零陆万玖仟元肆角零柒毫 1000780000069000.407
(1 行受影响)水如烟 2008-11-21
- 打赏
- 举报
SELECT * FROM [LzmtwWorks].[Helper].[ChineseNumReader] (
'壹仟兆柒仟捌佰亿零陆万玖仟.肆零柒'
,1)33 32 31 30 23 22 21 20 13 12 11 10 03 02 01 00 T1 T2 T3 Valid Value Input
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- -------------------------------------- --------------------------------------
1 0 0 0 7 8 0 0 0 0 0 6 9 0 0 0 4 0 7 1 1000780000069000.407 壹仟兆柒仟捌佰亿零陆万玖仟.肆零柒
(1 行受影响)水如烟 2008-11-21
- 打赏
- 举报
使用
1000780000069000.4065的读法,只保留小数点后三位,四舍五入
结果
1000780000069000.4065的读法,只保留小数点后三位,四舍五入
SELECT * FROM [LzmtwWorks].[Helper].[ChineseNumReader] (
'1000780000069000.4065'
,0)结果
33 32 31 30 23 22 21 20 13 12 11 10 03 02 01 00 T1 T2 T3 Valid Value Input
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- -------------------------------------- --------------------------------------
壹 零 零 零 柒 捌 零 零 零 零 零 陆 玖 零 零 零 肆 零 柒 1 壹仟兆柒仟捌佰亿零陆万玖仟.肆零柒 1000780000069000.407
(1 行受影响)
