September 12, 2014 at 12:38 pm
SELECT val
,STUFF(val, CHARINDEX('%', val)-1, 1, '') AS truncval
,LTRIM(CAST(ROUND(CAST(REPLACE(val, '%', '') AS DECIMAL(10,2)),1,1) AS DECIMAL(10,1))) + '%' AS truncval2
,LTRIM(CONVERT(DECIMAL(10,1), ROUND(CONVERT(DECIMAL(10,2), REPLACE(val, '%', '')),1,1))) + '%' AS truncval3
FROM (VALUES ('99.87%'), ('99.96%'), ('8.67%'), ('100.252%'), ('25.7'), ('0.3333')) as sample(val)
CAST and CONVERT versions will work even when percent sign is missing or there are more than 2 decimal places.
val truncval truncval2 truncval3
99.87% 99.8% 99.8% 99.8%
99.96% 99.9% 99.9% 99.9%
8.67% 8.6% 8.6% 8.6%
100.252% 100.25% 100.2% 100.2%
25.7 NULL 25.7% 25.7%
0.3333 NULL 0.3% 0.3%
September 12, 2014 at 1:30 pm
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
September 16, 2014 at 4:45 pm
If there's always at least one decimal place:
SELECT
value AS original_value,
SUBSTRING(value, 1, CHARINDEX('.', value) + 1) + '%' AS new_value
FROM (
SELECT '99.87%' AS value UNION ALL
SELECT '99.96%' UNION ALL
SELECT '8.67%' UNION ALL
SELECT '100.00%' UNION ALL
SELECT '.67%' UNION ALL
SELECT '0.67%'
) AS test_data
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 16, 2014 at 10:41 pm
ScottPletcher (9/16/2014)
If there's always at least one decimal place:
SELECT
value AS original_value,
SUBSTRING(value, 1, CHARINDEX('.', value) + 1) + '%' AS new_value
FROM (
SELECT '99.87%' AS value UNION ALL
SELECT '99.96%' UNION ALL
SELECT '8.67%' UNION ALL
SELECT '100.00%' UNION ALL
SELECT '.67%' UNION ALL
SELECT '0.67%'
) AS test_data
According to the statistics I posted earlier, this method is at least twice as fast as any of the others.
😎
September 17, 2014 at 8:56 am
Perhaps this:
SELECT val, SUBSTRING(val,1,CHARINDEX('.', val)+1)+'%'
FROM (VALUES ('99.87'), ('99.96'), ('8.67')) sampledata(val)
September 17, 2014 at 8:59 am
Perhaps this:
SELECT val, SUBSTRING(val,1,CHARINDEX('.', val)+1)+'%'
FROM (VALUES ('99.87'), ('99.96'), ('8.67')) sampledata(val)
September 18, 2014 at 10:01 am
Assuming that there are ALWAYS digits to the right of the decimal, then:
select val, left(val, charindex('.', val) + 1)
from (values
('99.87'), ('99.96'), ('8.67')) sampleData(val)
If there are no digits to the right of the decimal, or no decimal, and you always want ".0" in those cases, then you could use:
select val,
case when charindex('.', val) = len(val) then val + '0'
when charindex('.', val) = 1 then '0' + left(val, charindex('.', val) + 1)
when charindex('.', val) > 1 then left(val, charindex('.', val) + 1)
else val + '.0' end
from (values
('99.87'), ('9.87'), ('9.8'), ('9'), ('9.'), ('.87')) sampleData(val)
d
Don Simpson
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply