Copy Code code as follows:
--cast and CONVERT functions percentage
DECLARE @dec Decimal (5,3), @var varchar (a), @hun decimal (5,1)
Set @dec =0.025
Set @hun = @dec *100
Set @var =cast (@hun as varchar (20)) + '% '
Select @var
Convert---Decimal to percent function getpercentagestring
---tu 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 (a), @hun decimal (10,1)
SET @hun = @dec *100
SET @var =cast (@hun as varchar (20)) + '% '
Return @var
End
Go
--Test data coating Geovin Du
SELECT [dbo]. [Getpercentagestring] (0.05)
SELECT [dbo]. [Getpercentagestring] (0.25)
SELECT [dbo]. [Getpercentagestring] (1.25)
--1. Whether the query string contains non-numeric characters
SELECT PATINDEX ('%[^0-9]% ', ' 1235x461 ')
SELECT PATINDEX ('%[^0-9]% ', ' 12350461 ')
--2. Whether numeric characters are included in the query string
SELECT PATINDEX ('%[0-9]% ', ' Suyllgoo ')
SELECT PATINDEX ('%[0-9]% ', ' suyllg0o ')
DECLARE @dec Decimal (10,4), @var varchar (a), @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
--conversion of percentages to fractional functions Getpercentagenumber
---tu 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 (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 coating Geovin Du
SELECT [dbo]. [Getpercentagenumber] (' 5.23% ')
SELECT [dbo]. [Getpercentagenumber] ('%0.23 ')
