How to use the MID function using the Query Builder in VB.NET

[Guest]

Ok, I have a Field that has a combination of letter C and a Number that
increments by one.

example:
C1
C2
C3
C4
C5
C6
C7
C9
C9
C10
C11 ...

I would like to find the Maximun number using the query builder. I tried
using the Substring function but it didnt work, I also tried the LEFT and
RIGHT functions but didnt work for me.

I tried using the MID function but it gave an error saying 'MID' is not a
recognized function name.


Please I just would like to find any way to get the highest numer after the
letter.

Example: if I have C1 thru C34 I wold like the query to give me 34 as the
result.

Thanks for any suggestions.

 

[Oenone]

I would like to find the Maximun number using the query builder. I
tried using the Substring function but it didnt work, I also tried
the LEFT and RIGHT functions but didnt work for me.

Assuming this is T-SQL you're using (for a SQL-Server query), you can do it
using SubString.

The easiest way is to get it to return a large number of characters,
starting at character no. 2. Because there aren't enough characters in the
string, it'll just return whatever characters are available, which will be
the remainder of the string.

For example:

SELECT SUBSTRING('C1', 2, 99)
Result is '1'


SELECT SUBSTRING('C234', 2, 99)
Result is '234'

Hope that helps,

 

[Peter Proost]

If the records always will contain one letter at the beginning you can use
this query (tested on sql server 2000):

select max(cast(substring(YourField,2,len(YourField)-1) as integer))
FROM YourTable

hth Peter

 

[Jay B. Harlow [MVP - Outlook]]

Eduardo,
This sounds like a database question? Are you asking for an SQL method of
doing it? SQL for which database, SQL Server, Access, AS/400, Oracle, DB2,
FoxPro?


In VB.NET itself I would do something like:

Dim values() As String = {"C1", "C2", "C3", "C4", "C5", "C6", "C7",
"C9", "C9", "C10", "C11"}
Dim min As Integer = Integer.MaxValue
Dim max As Integer = Integer.MinValue
For Each value As String In values
max = Math.Max(max, CInt(value.Substring(1)))
min = Math.Min(min, CInt(value.Substring(1)))
Next
Debug.WriteLine(min, "min")
Debug.WriteLine(max, "max")

Hope this helps
Jay

| Ok, I have a Field that has a combination of letter C and a Number that
| increments by one.
|
| example:
| C1
| C2
| C3
| C4
| C5
| C6
| C7
| C9
| C9
| C10
| C11 ...
|
| I would like to find the Maximun number using the query builder. I tried
| using the Substring function but it didnt work, I also tried the LEFT and
| RIGHT functions but didnt work for me.
|
| I tried using the MID function but it gave an error saying 'MID' is not a
| recognized function name.
|
|
| Please I just would like to find any way to get the highest numer after
the
| letter.
|
| Example: if I have C1 thru C34 I wold like the query to give me 34 as the
| result.
|
| Thanks for any suggestions.

 

[Guest]

The Mid function is available by referencing the Microsoft.VisualBasic
namespace.

Disclaimer: I make no value judgement as to its use (and will not
participate in any ensuing holy war).

David Anton
www.tangiblesoftwaresolutions.com
Home of the Instant C# VB.NET to C# converter
and the Instant VB C# to VB.NET converter

 

[Guest]

Thanks a lot, It worked great.
you just made my day!

 

[Cor Ligthert]

Disclaimer: I make no value judgement as to its use (and will not

participate in any ensuing holy war).

LOL