Sqlserver customize the percentage conversion decimal function. For more information, see.
Sqlserver customize the percentage conversion decimal function. For more information, see.
The Code is as follows:
-- CAST and CONVERT functions Percentage
DECLARE @ dec decimal (5, 3), @ var varchar (10), @ hun decimal (5, 1)
Set @ dec = 0.025
Set @ hun = @ dec * 100
Set @ var = cast (@ hun as varchar (20) + '%'
Select @ var
--- Convert decimals to percentages function GetPercentageString
--- Tu juwen Geovin Du
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [GetPercentageString] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [dbo]. [GetPercentageString]
GO
CREATE function GetPercentageString
(
@ Dec decimal (10, 3)
)
Returns varchar (20)
AS
BEGIN
DECLARE @ var varchar (10), @ hun decimal (10, 1)
SET @ hun = @ dec * 100
SET @ var = cast (@ hun as varchar (20) + '%'
RETURN @ var
END
GO
-- Test Data tu juwen Geovin Du
SELECT [dbo]. [GetPercentageString] (0.05)
SELECT [dbo]. [GetPercentageString] (0.25)
SELECT [dbo]. [GetPercentageString] (1.25)
-- 1. query whether a string contains non-numeric characters
Select patindex ('% [^ 0-9] %', '1235x461 ')
Select patindex ('% [^ 0-9] %', '123 ')
-- 2. query whether a string contains numeric characters
Select patindex ('% [0-9] %', 'suyllgoo ')
Select patindex ('% [0-9] %', 'suyll1_o ')
DECLARE @ dec decimal (10, 4), @ var varchar (10), @ hun decimal (10, 4), @ I INT, @ K INT, @ S VARCHAR (20)
SET @ var = '5. 23%'
-- SELECT @ K = LEN (@ var)
-- SELECT @ S = LEFT (@ var, @ K-1)
SELECT @ S = RIGHT (@ var, 1)
-- SELECT @ S
SELECT @ I = CHARINDEX ('%', @ S)
IF @ I> 0
BEGIN
SET @ var = REPLACE (@ var, '% ','')
SET @ hun = CAST (@ var AS decimal (10, 4 ))
SET @ dec = @ hun/100
SELECT @ dec
END
ELSE
BEGIN
SELECT @ dec = NULL -- 'invalid data'
END
-- Convert percentages to decimal functions GetPercentageNumber
--- Tu juwen Geovin Du
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [GetPercentageNumber] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [dbo]. [GetPercentageNumber]
GO
CREATE function GetPercentageNumber
(
@ Var varchar (10)
)
Returns decimal (10, 4)
AS
BEGIN
DECLARE @ dec decimal (10, 4), @ hun decimal (10, 4), @ I INT, @ K INT, @ S VARCHAR (20)
SELECT @ S = RIGHT (@ var, 1)
-- SELECT @ S
SELECT @ I = CHARINDEX ('%', @ S)
IF @ I> 0
BEGIN
SET @ var = REPLACE (@ var, '% ','')
SET @ hun = CAST (@ var AS decimal (10, 4 ))
SET @ dec = @ hun/100
-- SELECT @ dec
END
ELSE
BEGIN
SELECT @ dec = NULL -- 'invalid data'
END
RETURN @ dec
END
GO
-- Test Data tu juwen Geovin Du
SELECT [dbo]. [GetPercentageNumber] ('5. 23% ')
SELECT [dbo]. [GetPercentageNumber] ('% 123 ')
