SQL Server - Delete Values after Decimal (non-int field)

Question

Have a ncarchar(MAX) field in SQL table. It has numbers such as 717.08064182582, 39.0676048113, etc. in which I need to only have 3 places after decimal. For instance 717.080, 39.067.

Without converting the field type, would like to get rid of those last n characters, however every row has different number of characters. I believe I could use ROUND (correct me if wrong), but would rather not.

`SELECT CASE WHEN PATINDEX('%.%', @nvar) > 0 THEN SUBSTRING(@nvar, 1, PATINDEX('%.%', @nvar) + 3) ELSE @nvar END` — JamieD77, Apr 22 '16 at 14:32
My question is why are you storing numbers as strings in the first place??? — Sean Lange, Apr 22 '16 at 14:41
@JamieD77 LOL. I don't think all EAV is evil but that could quite possibly be the situation. — Sean Lange, Apr 22 '16 at 14:58
Possible duplicate of [SQL Server replace, remove all after certain character](http://stackoverflow.com/questions/1668014/sql-server-replace-remove-all-after-certain-character) — Tab Alleman, Apr 22 '16 at 15:02
@SeanLange the column stores more than just numbers. In this instance, I am working only with the values that are numbers. — CarryTheZero09, May 03 '16 at 14:33

Jaydip Jadhav · Accepted Answer · 2016-04-22T14:32:24.433

1

Try this

SELECT CAST(ColumnName AS DECIMAL(18,3))

Without converting it data type As per @vkp Comment

SELECT SUBSTRING(ColumnName ,0,CHARINDEX('.', ColumnName )+4)

edited Apr 22 '16 at 14:32

answered Apr 22 '16 at 14:20

Jaydip Jadhav

12,179
6
24
40

*Without converting the field type* – Vamsi Prabhala Apr 22 '16 at 14:21
1

This will implicitly round, i.e. `717.08064182582` becomes `717.081`. I'm not sure that's what OP wants, in the example the requested result is `717.080` – HoneyBadger Apr 22 '16 at 14:21
@HoneyBadger: OP stated that he could use `ROUND`. – Tim Schmelter Apr 22 '16 at 14:23
But OP also stated "Without converting the field type". Conflicting information :P – HoneyBadger Apr 22 '16 at 14:26
@vkp Without converting how is it possible to get expected result? – Jaydip Jadhav Apr 22 '16 at 14:28
Remember, `SUBSTRING` is 1 based, not 0. That's why you have to use `+4` instead of `+3`, which feels more logical (to me anyway) – HoneyBadger Apr 22 '16 at 14:45

score 1 · Answer 2 · answered Apr 22 '16 at 14:35

select  CASE    WHEN    CHARINDEX('.', Your_column) > 0 
                THEN    SUBSTRING(Your_column, 1, CHARINDEX('.', Your_column) + 3)
                ELSE    Your_column
        END

this is similar as previous answers but more faster and safer

score 0 · Answer 3 · answered Apr 22 '16 at 14:25

0

Try this:

SELECT  SUBSTRING(Your_column, 1, PATINDEX('%.%', Your_column) + 3)
FROM    Your_Table

answered Apr 22 '16 at 14:25

HoneyBadger

14,750
3
34
48

SQL Server - Delete Values after Decimal (non-int field)

3 Answers3