Convert Error (varchar to int)

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

Transact-SQL (2000)

Convert Error (varchar to int)

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2005-08-30 : 15:25:21

I have one column named ContractNumber which is Varchar 10
It could have data such as:
A101
A102
A103

I need to get the next number, but the alpha prefix is throwing me.

This is what I have so far:
SELECT TOP 1
RIGHT(ContractNumber,LEN(ContractNumber -1)) AS TopContractNumber
FROM Event
ORDER BY ContractNumber DESC

This is not working. I am getting a syntax error converting A103 from varchar to int. I have tried converting just about every piece of this line and I am not doing something right. If anyone has a solution as to how to extract the number out of that contract number to get the greatest one, I would so much appreciate it.

Thanks

mfemenel
Professor Frink

1421 Posts

Posted - 2005-08-30 : 15:32:50

should read len(contractnumber)-1) and then you've got it. You can't subtract 1 from contract number. You have to let the LEN function return the value first then subtract 1.

Mike
"oh, that monkey is going to pay"

JAdauto
Posting Yak Master

160 Posts

Posted - 2005-08-30 : 15:35:32

Oh my goodness. I cannot believe it was a ). If you would have seen all the Convert stuff I was putting into that thing to try to convert it, you would have a had good laugh for the day.

Thanks so much!!!

mfemenel
Professor Frink

1421 Posts

Posted - 2005-08-30 : 16:08:14

Yeah, we've all been there. It's usually the stupid simple things that trip me up too.

Mike
"oh, that monkey is going to pay"

JAdauto
Posting Yak Master

160 Posts

Posted - 2005-09-06 : 22:33:21

Its me.. I'm back again with this same issue. This is all working beautifully but because I setup my contract numbers as A101, A102, A103. If contract numbers go from say A1, A2, A3..... A34, A35, A36.. then the next logical contract number would be 37, but it is returning 9.

I have tried converting the value such as this:

SELECT TOP 1 Cast(RIGHT(ContractNumber, Len(ContractNumber) - 1)as int(4)) As TopContractNumber
FROM Event
ORDER BY ContractNumber desc

but that does not do anything.

So then I thought maybe the Contract number in the order by was where I was having problems so I (dont laugh) tried:

SELECT TOP 1 Cast(RIGHT(ContractNumber, Len(ContractNumber) - 1)as int(4)) As TopContractNumber
FROM Event
ORDER BY Cast(RIGHT(ContractNumber, Len(ContractNumber) - 1)as int(4)) desc

But I get error:
Invalid length parameter passed to the substring function.

Anyone have any thoughts on this. I know there has to be away to get the top number and I know it is returning 9 because it is not a numeric value it is evaluating.. I just cant figure out how to make it be that.

Thanks so much

JAdauto
Posting Yak Master

160 Posts

Posted - 2005-09-08 : 00:42:50

I dont know what is wrong with what I was trying to do, but I changed it up and this seems to work:

SELECT top 1
CAST(SUBSTRING(ContractNumber,2,9) as BIGINT(8)) As TopContractNumber
FROM Event
ORDER BY CAST(SUBSTRING(ContractNumber,2,9) as BIGINT(8)) DESC

If anyone sees any problems with this, please let me know. I am about to send this to testing again and am hoping to find any problems before they do.

Thanks so much

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 01:43:50

Try this

Select ContractNumber from Event order by len(ContractNumber),ContractNumber

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources