SELECT MAX(VAL(id))+1 AS id FROM stock INTO newid

[madurrani]

ID examples 1110I, 1100U, 1112A

In foxpro & visual foxpro I used the following command to get the new id for alpha numeric codes

SELECT MAX(VAL(id))+1 AS id FROM stock INTO newid

It would return 1113 in the above example ids, in foxpro/visual foxpro. How I can use this command in VB.NET/SQL


Thanks

 

[bborissov]

That is SQL Server. There is no such things like INTO CURSORS, INTO TABLE or INTO ARRAY. You get the recordset.
Are your ALWAYS the first four chars are numerics?
Is it possible that ID to become 1111111U?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

This looks worse than it it.

You should be able to use this to get your NewId.

Select	Max(Left(Id, PatIndex('%[^0-9]%', Id + 'X')-1)) + 1
From	stock

Let me try to explain it. Working from the inside.

[tt][blue]Id + 'X'[/blue][/tt]
We need to make sure there is at least 1 alpha character in the data. If there wasn't, then other parts (getting to that soon) would error out.

[tt][blue]PatIndex('%[^0-9]%', Id + 'X')[/blue][/tt]
PatIndex will return an integer representing the first position that matches the search. In this case, we are searching for [red]%[^0-9]%[/red] Read this as, the first character found anywhere in the string that is NOT the characters 0 through 9.

[tt][blue]Left(Id, PatIndex('%[^0-9]%', Id + 'X')-1) [/blue][/tt]
Now, take the left part of the string. The length of the resulting string is the PatIndex value -1. If we had not put the [!]+ 'X'[/!], and there was no alpha character, then PatIndex would return 0. Left(id, 0-1) would give an error.


[tt][blue]Max(Left(Id, PatIndex('%[^0-9]%', Id + 'X')-1)) + 1[/blue][/tt]
Lastly, take the max + 1 of the resulting value.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom