我怎么能为我的客户这样做 [英] how can i do this for my client

问题描述

DECLARE @Number NUMERIC(18,6)
SET @Number = '99.1230000'
SELECT  CAST(@Number AS NUMERIC(18,6))
               ,CONVERT(NUMERIC(18,6), @Number)
               ,REPLACE(RTRIM(REPLACE(@Number, '0', ' ')), ' ', '0') [REQUIRED VALUE]
               ,ISNULL(PARSENAME(REPLACE(RTRIM(REPLACE(@Number, '0', ' ')), ' ', '0'),1) ,0) [PARSE VALUE]
               ,LEN(CONVERT(VARCHAR,ISNULL(PARSENAME(REPLACE(RTRIM(REPLACE(@Number, '0', ' ')), ' ', '0'),1) ,0))) [PARSE VALUE LEN]
               , CASE LEN(CONVERT(VARCHAR,ISNULL(PARSENAME(REPLACE(RTRIM(REPLACE(@Number, '0', ' ')), ' ', '0'),1) ,0)))
                 WHEN 0 THEN CONVERT(NUMERIC(18,2),REPLACE(RTRIM(REPLACE(@Number, '0', ' ')), ' ', '0'))
                 WHEN 1 THEN CONVERT(NUMERIC(18,2),REPLACE(RTRIM(REPLACE(@Number, '0', ' ')), ' ', '0'))
                 ELSE REPLACE(RTRIM(REPLACE(@Number, '0', ' ')), ' ', '0')
                 END [FINAL VALUE]

目前我使用此查询作为我客户的要求



基本上它不是直到现在



i需要我的客户输入123.012300

然后我只需要显示123.0123



当他输入123.0000

i需要向他显示123.00



当他输入123.95001

然后我需要显示123.95001



当他输入123.950010

然后我只需要显示123.95001



当他进入123.000200

然后我只需要显示123.0002



我该怎么做？

currently i use this query as the requirment of my client

basically it is not comlete til now

i need when my client enter 123.012300
then i need to show 123.0123 only

and when he enter 123.0000
i need to show him 123.00

and when he enter 123.95001
then i need to show 123.95001

and when he enter 123.950010
then i need to show 123.95001 only

and when he enter 123.000200
then i need to show only 123.0002

how can i do this?

推荐答案

使用它，

use this,

Declare @myvalue varchar(50),
        @Price Varchar(50)

Set @Price = '9' --give input in this line

set @price = case when patindex('%.%',@Price)=0 then @price + '.00' else @price end

set @Myvalue = reverse(substring(@Price,patindex('%.%',@Price)+1,len(@Price)))


select case when len(substring(result,2,len(result)-2))=1 then result + '0' else result end as result from
(
    SELECT
    case
    When  patindex('%.%[1-9]%',@price) = 0 Then
        substring(@price,1,patindex('%.%',@price)-1 )+ '.' + '00'
    else
        substring(@price,1,patindex('%.%',@price)-1) + '.' +  Reverse(substring(@Myvalue,patindex('%[1-9]%',@Myvalue),len(@Myvalue)))
    END  as result
) as a

快乐编码！

：）

Happy Coding!
:)

请参阅以下链接



从列中删除前导零

Refer below link

Removing Leading Zeros From Column

这篇关于我怎么能为我的客户这样做的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！