CONVERT(varchar(10), @myInt) fails

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CONVERT(varchar(10), @myInt) fails

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-06-03 : 05:12:02

I've got some string stuff that I need to convert to an INT.

So I used IsNumeric(@strValue) to make sure there weren't any rogue values.

And then some bright-young-spark at the client tried putting "1.1" in.

Blow me down if CONVERT from Varchar to Int doesn't fail on that ...

So I've resorted to CONVERTing it to FLOAT first, then INT <Snort!>

I suppose I could use

IF @strValue LIKE '%[^0-9]%' ....

to find the non-Ints. I'd have to cope with a leading minus-sign though.

Here's an example of what ruined my day:


SET NOCOUNT ON
GO
DECLARE	@strValue	varchar(10),
	@intLoop	int

SELECT	@intLoop = 1

WHILE	@intLoop <= 3
BEGIN
	IF @intLoop = 1 SELECT @strValue = '1'
	IF @intLoop = 2 SELECT	@strValue = 'A'
	IF @intLoop = 3 SELECT	@strValue = '0.1'


	IF IsNumeric(@strValue) = 0
	BEGIN
		SELECT	@strValue, 'Not numeric'
	END
	ELSE
	BEGIN
		SELECT	[Value] = @strValue,
			[With FLOAT] = CONVERT(int, CONVERT(float, @strValue))
-- The "1.1" example will fail here:
		SELECT	[Value] = @strValue,
			[Only INT] = CONVERT(int, @strValue)
	END

	SELECT	@intLoop = @intLoop + 1
END
GO

SET NOCOUNT ON
GO

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-03 : 07:12:55

Yes. Decimal point is part of float but not part of int value
select the digits upto decimal point using substring

DECLARE @strValue varchar(10)
SELECT @strValue = '1.1'
select [Only Int]=convert(int,substring(@strvalue,1,(charindex('.',@strvalue)-1)))

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-03 : 08:33:42

you could also just do this:

declare @n varchar(50)
set @n = '1.1'
select @n, convert(int, floor(@n))

Go with the flow & have fun! Else fight the flow

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-03 : 12:17:41

You could also make the CLIENT APP validate data before it comes in.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Kristen
Test

22859 Posts

Posted - 2005-06-05 : 03:29:12

So is FLOOR or SUBSTRING better/faster/more desireable than

CONVERT(int, CONVERT(float, @strValue))

I don't like the sound of SUBSTRING as I dunno what other non-int strings there are that might need additional code - e.g. '1E2'

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2005-06-05 : 07:28:55

IsNumeric(@strValue)
is not a good way of filtering things that can't be converted.
These will all pass that test
123
$123
1e23
1.23

And there's no numeric datatype to which you can convert all those values - which means that you will have to do some other checking on the input data to prevent convertion errors if you want to cater for bad input data.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2005-06-05 : 08:57:39

Thanks Nigel.

In that case I think I fancy rejecting the value if it is passes this test:

IF @strValue LIKE '%[^0-9]%' ....

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources