Solved

# How to convert number 123.8 to 1238, removing decimal, in SQL query?

Posted on 2016-11-14
19 Views
How do I remove the decimal point in a string using a SQL statement?

Also, the result must result in a Char field with 7 spaces.

So 123.8 would be 1238xxx with x's being blank spaces in a fixed width file.

or D02.3 as D023xxx with x's being blank spaces always to the right of the string.

I am using SQL 2008.
0
Question by:Becky Edwards

LVL 34

Accepted Solution

Brian Crowe earned 500 total points
ID: 41887046
GT/LT just included to illustrate the spaces...

``````DECLARE @Test	VARCHAR(30) = '123.8'

SELECT '>' + LEFT(CAST(REPLACE(@Test, '.', '') AS CHAR(7)) + '       ', 7) + '<'
``````
0

LVL 17

Expert Comment

ID: 41887137
Try, Replace . with '' and use RTRIM to remove spaces from the right hand side and the add SPACE(7).

Note - RTRIM is used because if we have value like '128.8 ', then we cannot add 7 spaces directly. If we add then we will get 8 spaces. So first we need trim the spaces from the right hand side and then add the 7 spaces we need.

Note - | (Appended pipe) so that we can check that there are no spaces after the string.

``````DECLARE @ AS VARCHAR(100) = '123.8    '

SELECT '|' + RTRIM(REPLACE(@,'.','')) + SPACE(7) + '|'

GO
DECLARE @ AS VARCHAR(100) = 'D02.3    '

SELECT '|' + RTRIM(REPLACE(@,'.','')) + SPACE(7) + '|'
``````

Output

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|1238       |

(1 row(s) affected)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|D023       |

(1 row(s) affected)

Code for you with out pipes

``````DECLARE @ AS VARCHAR(100) = '123.8    '

SELECT RTRIM(REPLACE(@,'.','')) + SPACE(7)

GO
DECLARE @ AS VARCHAR(100) = 'D02.3    '

SELECT RTRIM(REPLACE(@,'.','')) + SPACE(7)
``````

Hope it helps !!
0

LVL 14

Expert Comment

ID: 41887950
Hello,

You can try this

``````SELECT LEFT(REPLACE(COLUMN,'.','') + '       ',7) FROM TABLE
``````
0

Author Closing Comment

ID: 41888409
These all worked!
I gave the points to the first person who answered.

Thank you.
0

LVL 17

Expert Comment

ID: 41889376
No problem :) , Thanks Becky !!

Regards,
Pawan
0

## Featured Post

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.