cast/convert with no trailing zeroes

Question

cast/convert with no trailing zeroes

el0

Valued Member

Points: 71
More actions
June 30, 2010 at 2:55 am

#226276

I trying to solve a problem which i have a list with numbers as varchar(50) like
1.1
1.12
3.12
6.3
6.11
6.112
...
I want to convert them to numbers without trailing zeroes so the list should be as above and not as below
1.100
1.120
3.120
6.300
6.110
6.112
Any suggestions how this should be done?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I think you're confusing the way data should be stored internally with how it should be presented to the users.

It's not a conversion issue, it's just how you format data on the application side. Check your language documentation on how to format numeric values on the app side.

-- Gianluca Sartori

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

I agree with Gianluca. But if you insist on doing it in SQL, it can be done like this

DECLARE @test-2 AS TABLE(

id INT IDENTITY,

NUMBER DECIMAL(4, 3))

INSERT INTO @test-2(NUMBER)

SELECT 1.100

UNION ALL SELECT 1.120

UNION ALL SELECT 3.120

UNION ALL SELECT 6.300

UNION ALL SELECT 6.110

UNION ALL SELECT 6.112

SELECT *,

REPLACE(Rtrim(REPLACE(CONVERT(CHAR, NUMBER), '0', '

')), ' ', 0)

FROM @test-2

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

skcadavre (6/30/2010)
I agree with Gianluca. But if you insist on doing it in SQL, it can be done like this
DECLARE @test-2 AS TABLE(
id INT IDENTITY,
NUMBER DECIMAL(4, 3))
INSERT INTO @test-2(NUMBER)
SELECT 1.100
UNION ALL SELECT 1.120
UNION ALL SELECT 3.120
UNION ALL SELECT 6.300
UNION ALL SELECT 6.110
UNION ALL SELECT 6.112
SELECT *,
REPLACE(Rtrim(REPLACE(CONVERT(CHAR, NUMBER), '0', '
')), ' ', 0)
FROM @test-2

You might want to try the following to see why that doesn't work...

DECLARE @test-2 AS TABLE(

id INT IDENTITY,

NUMBER DECIMAL(4, 3))

INSERT INTO @test-2(NUMBER)

SELECT 1.100

UNION ALL SELECT 1.120

UNION ALL SELECT 3.120

UNION ALL SELECT 6.300

UNION ALL SELECT 6.110

UNION ALL SELECT 6.112

UNION ALL SELECT 6.102

SELECT *,

REPLACE(Rtrim(REPLACE(CONVERT(CHAR, NUMBER), '0', '

')), ' ', 0)

FROM @test-2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

Ah... I believe I see... the forum code (or something else) added a spurious ^C_R character. If you get rid of it, the previous code works just fine.

DECLARE @test-2 AS TABLE(

id INT IDENTITY,

NUMBER DECIMAL(4, 3))

INSERT INTO @test-2(NUMBER)

SELECT 1.100

UNION ALL SELECT 1.120

UNION ALL SELECT 3.120

UNION ALL SELECT 6.300

UNION ALL SELECT 6.110

UNION ALL SELECT 6.112

UNION ALL SELECT 6.102

UNION ALL SELECT 6.000

SELECT *,

REPLACE(Rtrim(REPLACE(CONVERT(CHAR, NUMBER), '0', ' ')), ' ', 0)

FROM @test-2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Jeff Moden (7/3/2010)
Ah... I believe I see... the forum code (or something else) added a spurious ^C_R character. If you get rid of it, the previous code works just fine.

The "something else" is the cause. . . me! Seems I made an error when copying my code into the forum, whoops :hehe:

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/