Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 String to 2 decimal

Author  Topic 

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2015-01-20 : 03:46:49
I have varchar column with decimal values and would like to chop 2 points (Eg. 8.00000000 To 8.00)

Can anyone help with query ?

Thanks !

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-20 : 07:28:04
Something like this should do the trick.


DECLARE @Temp AS TABLE (String VARCHAR(50))

INSERT INTO @Temp (
String
) VALUES ('8.000000'),
('10.12000'),
('125.9900')

SELECT String
, LEFT(String, CHARINDEX('.', String)) + SUBSTRING(String, CHARINDEX('.', String) + 1, 2) AS FormattedString
FROM @Temp


Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-20 : 11:51:41
[code]

DECLARE @Temp AS TABLE (String VARCHAR(50))

INSERT INTO @Temp ( String )
SELECT '8.000000' UNION ALL
SELECT '10.12000' UNION ALL
SELECT '125.9900' UNION ALL
SELECT '450'

SELECT String
, LEFT(String, CHARINDEX('.', String + '.') + 2)
FROM @Temp

[/code]
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2015-01-20 : 13:34:45
How about something like
DECLARE @Temp AS TABLE (String VARCHAR(50))

INSERT INTO @Temp ( String )
SELECT '8.000000' UNION ALL
SELECT '10.12000' UNION ALL
SELECT '125.9900' UNION ALL
SELECT '450'

SELECT String, CAST(CAST(String AS DECIMAL(10,2)) AS VARCHAR(50)) AS NewVal
FROM @Temp

Unfortunately it adds two decimals to the 450.

djj
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-21 : 02:30:52
It also will abend if any column contains non-decimal data.
Go to Top of Page
   

- Advertisement -