StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONumber lookup and sorting in Informix
text
Body
copied!<p>I have a table in an IBM Informix database wherein there's a column 'level' of data type CHAR(15). If I do a SELECT DISTINCT on that column, the top 5 results are:</p> <ul> <li>UNKNOWN</li> <li>ROOKIE</li> <li>LEVEL 1</li> <li>LEVEL 2A</li> <li>LEVEL 2B</li> </ul> <p>My intention is to write a query that will sort the results in ascending order of the <strong>number</strong> in that column. I have implemented it via VB.NET code, but was wondering if I can do it in the query.</p> <pre><code>' Results is a generic list of a class with properties corresponding to column names ' I am using IDataReader to go through the queried rows and load the data to 'results' results = results.OrderBy(Of Integer)(Function(p) Utilities.ExtractNumber(p.Level)) </code></pre> <p>This is how the ExtractNumber method looks like:</p> <pre><code>Public Shared Function ExtractNumber(ByVal expr As String) As Integer Dim number As Integer = 0 Dim character As Char Dim startPos As Integer = -1 Dim endPos As Integer = -1 For pos = 0 to expr.Length - 1 character = expr(pos) If Char.IsDigit(character) And startPos = -1 Then startPos = pos Else If Not Char.IsDigit(character) And startPos > -1 Then endPos = pos Integer.TryParse(expr.Substring(startPos, endPos - startPos), number) Exit For End If Next 'Number extends till end of string If startPos > -1 And endPos = -1 Then Integer.TryParse(expr.Substring(startPos), number) EndIf End Function </code></pre> <p>What my code does is it looks up for the first occurrence of a number in the string for each value in that column. If there are more than one numbers in the string (for example ALPHA 1C 211", it will return 1, which is the first number. If there are no numbers present, like in "unknown", it will just return 0.</p> <p>What I did above could be easily done using, say, Regex.Split but I didn't use that as it was returning a string array with empty elements preceding the number.</p> <p>Is there a way I can do this number extraction in an SQL query? Maybe use some kind of string manipulation to get rid of everything but the first number? I'm not allowed to write a function though, so I have to do all this in one query, if at all possible. Any pointers?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload