Hi Roy,

Ahh thanks for that. Maybe I shouldn't have simplified my example for
the newsgroups.

One way I've thought about doing it is to use the replace command, but
it would be huge...

Replace('A' WITH '' IN Replace ('B' WITH '' IN Replace ('C' WITH '' IN
..... etc)

Allowing for every character (symbols too) would make for one super long
SQL statement.

Maybe I'm better off going back to a real field and doing the
calculation back in my app?

Cheers

Adam.

Adam

>Ahh thanks for that. Maybe I shouldn't have simplified my example for
>the newsgroups.

True. SO what is the real requirement?

>One way I've thought about doing it is to use the replace command, but
>it would be huge...
>
>Replace('A' WITH '' IN Replace ('B' WITH '' IN Replace ('C' WITH '' IN
>.... etc)
>
>Allowing for every character (symbols too) would make for one super long
>SQL statement.
>
>Maybe I'm better off going back to a real field and doing the
>calculation back in my app?

I refuse to comment until I see the actual requirement

Roy Lambert [Team Elevate]

> I refuse to comment until I see the actual requirement

LOL - the actual requirement is still the same. To sort by Numeric
order. What I neglected to say (honestly it didn't even cross my mind)
is that I want to allow for the user to set whatever prefix they want.
(And have the option to change).

So the prefix might be "A" or "ABC" - a variable length.

I'm truely sorry about neglecting this... it didn't even cross my mind
until I went to implement it.

Adam

>LOL - the actual requirement is still the same. To sort by Numeric
>order. What I neglected to say (honestly it didn't even cross my mind)
>is that I want to allow for the user to set whatever prefix they want.
>(And have the option to change).
>
>So the prefix might be "A" or "ABC" - a variable length.
>
>I'm truely sorry about neglecting this... it didn't even cross my mind
>until I went to implement it.

I have posted a solution which will work until there are more than 6 alphas as a prefix.

Roy Lambert [Team Elevate]

Roy

>
> I'm not sure how complex the code in a column definition can be, I do know it can't use a user defined function. As I
have time I'll play around and see if I can come up with something. Alternatively if John Hay is feeling bored......
>

OK you tempted me

Imperfect I'm sure but seems to work.  Use a function to find the position of the number

CREATE FUNCTION "getnumber" (IN "infld" VARCHAR(100) )
RETURNS INTEGER
BEGIN
declare fld varchar(100);
declare i integer;
declare found boolean;
set fld=rtrim(infld);
set i = length(fld);
set found = false;
if (i > 0) and ((substr(fld,i,1) >= '0') and (substr(fld,i,1) <= '9')) then
 repeat
   if ((substr(fld,i,1) >= '0') and (substr(fld,i,1) <= '9')) then
     set i = i-1;
   else
     set found=true;
   end if;
 until found or (i < 1)  end repeat;
 set i = i+1;
else
 set i = 0;
end if;
if i > 0 then
 set i = cast(substring(fld,i,20) as integer);
end if;
return i;
END

Use a generated column in the table to hold the number.

ALTER TABLE "TABLENAME"
Add COLUMN "Number" INTEGER GENERATED ALWAYS AS getnumber(id);


John

John


Fascinating. I'm especially gobsmacked by the fact that a generated column can use the function but a computed one can't. I hadn't realised there was a difference or if Tim had told me I'd forgotten.

Brilliant as always.

Roy Lambert

Roy
> Fascinating. I'm especially gobsmacked by the fact that a generated column can use the function but a computed one
can't. I hadn't realised there was a difference or if Tim had told me I'd forgotten.
>

I was surprised too.  I'm sure there is a good reason - maybe standards <ducks>

John

John


>I was surprised too. I'm sure there is a good reason - maybe standards <ducks>

I prefer chicken or turkey

Roy Lambert

Hello Guys!

>> Worth a shot I suppose. But my line of thinking tells me that computed
>> fields won't be indexed as they're created on the fly. Doing a select /
>> sort on a non-indexed field in my previous table could take 20+ seconds
>> to return a result. (There's a bit of data in there).
>
> You can create an index on a computed field. I assume that in that
case the real data is stored in the index. Think back to dBase and
calculated indices.
>
> Roy Lambert [Team Elevate]

Well, there a super easy way. Tim should add "StrCmpLogicalW" sorting
support

http://msdn.microsoft.com/en-us/library/bb759947%28v=vs.85%29.aspx

Hi John,

Incredible! Thanks for that. I see that there's a lot more to EDB than
meets the eye!

Cheers

Adam.