Truncating a numeric

Question

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I don't see why you'd want to remove the 0's from an int since it would alter it value but you can surely do something like this :

DECLARE @I AS INT

SET @I = 120400

SELECT CONVERT(INT, REVERSE(CONVERT(VARCHAR(20), CONVERT(INT, REVERSE(CONVERT(VARCHAR(20), @I)))))) AS Replaced

I'm sure there's a faster way to do this than doing 4 converts but at least that works for now !

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 2

Not sure why you would want to do this ... but how about

SELECT CASE @TheNumber % 10

WHEN 0 THEN @TheNumber/10

ELSE @TheNumber

END

?

>L<

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That only truncates the last 0.

I was thinking about a similar solution but it was involving a function and a while loop.

But I'm sure there's a way to trap the first last 0 with pathindex but I don't have time to experiment.

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 4

OH!!! You said "remove the trailing 0". If you meant "remove the trailing 0s" why didn't you say so <s>?

Try this:

REPLACE(RTRIM(REPLACE(CAST(@x AS VARCHAR),'0',SPACE(1))),SPACE(1),'0')

>L<

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 5

I forgot the outer CAST back to INT but you knew that, right <G>? Anyway, works fine.

SELECT

CAST(REPLACE(RTRIM(REPLACE(CAST(@x AS VARCHAR),'0',SPACE(1))),

SPACE(1),'0')

AS Int)

Opus Default port Points: 1419 More actions · Answer 6

Thanks for all the replies. The data is a teacher id; wish it would have been a char field.