abhas (9/11/2014)
Hi all,in addition above.
Hi,
put varchar datatype, why because actual data is like: Sorry for not mentioning earlier.
99.87%, 99.96%, 8.67%
and out put want as
99.8%
99.9%
8.6%
Thanks,
Abhas.
Here is a quick solution, should help get you passed the hurdle
😎
USE tempdb;
GO
;WITH TEST_SET AS
( SELECT * FROM
(VALUES
('48.33%')
,('73.36%')
,('6.03%')
,('49.6899%')
,('0.33%')
,('38.43%')
,('38.434%')
,('38.4345%')
,('38.43456%')
,('38.434567%')
,('38.4345678%')
,('97.97%')
,('4.37%')
,('60.91%')
,('21.25%')
)AS X(CHPE))
SELECT
CHPE
,STUFF(CHPE,CHARINDEX('.',CHPE,1) + 2,CHARINDEX(CHAR(37),CHPE,1) - (CHARINDEX('.',CHPE,1) + 2),'') AS CHOPPED
,SUBSTRING(CHPE,1, CHARINDEX('.',CHPE) + 1) + CHAR(37) AS CHOPPED_SUBS
,STR(ROUND(CONVERT(FLOAT,REPLACE(CHPE,CHAR(37),''),3),1,1),8,1) + CHAR(37) AS FLOAT_STR
FROM TEST_SET TS;
Results
CHPE CHOPPED CHOPPED_SUBS FLOAT_STR
----------- --------- ------------ ---------
48.33% 48.3% 48.3% 48.3%
73.36% 73.3% 73.3% 73.3%
6.03% 6.0% 6.0% 6.0%
49.6899% 49.6% 49.6% 49.6%
0.33% 0.3% 0.3% 0.3%
38.43% 38.4% 38.4% 38.4%
38.434% 38.4% 38.4% 38.4%
38.4345% 38.4% 38.4% 38.4%
38.43456% 38.4% 38.4% 38.4%
38.434567% 38.4% 38.4% 38.4%
38.4345678% 38.4% 38.4% 38.4%
97.97% 97.9% 97.9% 97.9%
4.37% 4.3% 4.3% 4.3%
60.91% 60.9% 60.9% 60.9%
21.25% 21.2% 21.2% 21.2%
For completeness here are stats for 1000000 records
2014-09-12 21:35:03.4892822
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'NULL_STUFF'. Scan count 1, logical reads 4063, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1545 ms, elapsed time = 1580 ms.
2014-09-12 21:35:05.0693726
Table 'NULL_STUFF'. Scan count 1, logical reads 4063, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 686 ms, elapsed time = 713 ms.
2014-09-12 21:35:05.7904138
Table 'NULL_STUFF'. Scan count 1, logical reads 4063, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1560 ms, elapsed time = 1560 ms.
2014-09-12 21:35:07.3495030
Edit: added two more options and stats