Invalid length parameter passed to the substring

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 2008 Forums

Transact-SQL (2008)

Invalid length parameter passed to the substring

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-10-10 : 01:59:09

Hi All,

I got this error Invalid length parameter passed to the substring function, so i add in control case when len(Rate)>3 and 1<right(Rate)<9 then. Am i doing correct?

I noticed if Rate = 0.1, 0.2,0.10,0.20 not working.
select SUBSTRING( CAST(cast(1 as float)/(cast(0.2 as float)) AS VARCHAR),1,CHARINDEX ('.',CAST(cast(1 as float)/(cast(0.2 as float)) AS VARCHAR))-1)

0.11,0.19,0.21,0.29 then working fine.
select SUBSTRING( CAST(cast(1 as float)/(cast(0.21 as float)) AS VARCHAR),1,CHARINDEX ('.',CAST(cast(1 as float)/(cast(0.21 as float)) AS VARCHAR))-1)

Please advise.

set @Years= (
select case when len(Rate)>3 and 1<right(Rate)<9 then SUBSTRING( CAST(cast(1 as float)/(cast(Rate as float)) AS VARCHAR),1,CHARINDEX ('.',CAST(cast(1 as float)/(cast(Rate as float)) AS VARCHAR))-1)
else 1/Rate end from [tblTypeSetup] t
inner join tblMethod m on m.MethodID=t.MethodID
where t.[Description]=@type
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 02:00:52

what is the data type for column "Rate" ?

can you explain what are you trying to do here ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 02:08:42

are you trying to do this ?

floor(1 / Rate)

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 02:16:52

floor(1 / nullif(Rate,0))

if there's a chance of rate being 0 to avoid divide by 0 error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-10-10 : 03:37:29

Great!!! Thank you very much KHTan & Visakh.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-10-10 : 03:48:30

Another question, how can i check if 1/Rate got decimal place or not? No decimal => mean result='False' else result='True'.

Thank you.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-10-10 : 04:16:37

I try to used select case when (1/Rate)%1=0.000000 then

I got this error:-
The data types float and int are incompatible in the modulo operator.

Please Advise. TQVM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 04:28:18

[code]
select case when floor(1/Rate)=(1/Rate) then 'False' else 'True' end
..
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-10 : 04:56:18

One thing; why would you possibly want to use the float datatype?? Unless you're calculating proton masses or the distance between galaxies decimal would be the far better choice.

Read about the float data type here (especially this part: "Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.") ->
http://msdn.microsoft.com/en-us/library/ms173773.aspx

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources