Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

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.
0
Becky Edwards
Asked:
Becky Edwards
1 Solution
 
Brian CroweDatabase AdministratorCommented:
GT/LT just included to illustrate the spaces...

DECLARE @Test	VARCHAR(30) = '123.8'

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

Open in new window

0
 
Pawan KumarDatabase 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) + '|'

Open in new window


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)

Open in new window


Hope it helps !!
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

You can try this

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

Open in new window

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

Thank you.
0
 
Pawan KumarDatabase ExpertCommented:
No problem :) , Thanks Becky !!

Regards,
Pawan
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now