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]
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?