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

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.
###### Who is Participating?

x

GT/LT just included to illustrate the spaces...

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

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

Database ExpertCommented:
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

Hello,

You can try this

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

Epic Clarity DeveloperAuthor Commented:
These all worked!
I gave the points to the first person who answered.

Thank you.
0

Database ExpertCommented:
No problem :) , Thanks Becky !!

Regards,
Pawan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.