String to 2 decimal - SQL Server Forums

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

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]

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

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-21 : 02:30:52

It also will abend if any column contains non-decimal data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources