Becky Edwards
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello,
You can try this
You can try this
SELECT LEFT(REPLACE(COLUMN,'.','') + ' ',7) FROM TABLE
ASKER
These all worked!
I gave the points to the first person who answered.
Thank you.
I gave the points to the first person who answered.
Thank you.
No problem :) , Thanks Becky !!
Regards,
Pawan
Regards,
Pawan
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.
Open in new window
Output
--------------------------
|1238 |
(1 row(s) affected)
--------------------------
|D023 |
(1 row(s) affected)
Code for you with out pipes
Open in new window
Hope it helps !!