Hi,

I know it's a longshot, but I'm wondering if their is a way in SQL to pull
out just the numeric fields from a string column.

Ie - if the field is ABC123, I want the result to only be 123.

If even more possible, if the result was 12ABC123, I would prefer the result
to be just 123, but if that's not possible, but I could get the result
12123 - that would suffice.

Is this possible with SQL 'as is', or could/should this be done using custom
functions?

Thanks & Regards

Adam.

Adam


I'll be fascinated to see what one of the SQL guru's comes up with but my vote would be custom functions - probably easier to write and understand.

Roy Lambert

> I'll be fascinated to see what one of the SQL guru's comes up with but my
vote would be custom functions - probably easier to write and understand.

I guess it'll be time for me to write my first custom function then.  Unless
of course, some guru comes up with something wierd and wonderful.

Thanks Roy

Adam.

Adam


As they currently stand they're a doddle. I'm waiting to see what they metamorphose into in ElevateDB.

Roy Lambert

Roy,

<< As they currently stand they're a doddle. I'm waiting to see what they
metamorphose into in ElevateDB. >>

Basically they're the same thing with the additional option of specifying
them using SQL PSM instead of Delphi code.

--
Tim Young
Elevate Software
www.elevatesoft.com

Out of curiosity, does sorting / ordering the result of a query on a custom
function have any changes to it's performance, as opposed to ordering on a
indexed field, or are index performances only based on joins and where
clauses?

Cheers

Adam.

Adam,

<< Out of curiosity, does sorting / ordering the result of a query on a
custom function have any changes to it's performance, as opposed to ordering
on a indexed field, or are index performances only based on joins and where
clauses? >>

No.  DBISAM only allows you to order on SELECT columns in the ORDER BY, so
the ORDER BY is simply ordering on column values that have already been
calculated and stored.  There is, however, some additional time taken during
the population of the SELECT column that includes the custom function,
depending upon what the custom function is actually doing.

--
Tim Young
Elevate Software
www.elevatesoft.com

Adam
> I know it's a longshot, but I'm wondering if their is a way in SQL to pull
> out just the numeric fields from a string column.
>
> Ie - if the field is ABC123, I want the result to only be 123.
>
> If even more possible, if the result was 12ABC123, I would prefer the
result
> to be just 123, but if that's not possible, but I could get the result
> 12123 - that would suffice.

I know its a bit contrived but something like the following (assuming column
length of 10)  should do what you want

select
if(cast(substring(account from 1 for 10) as integer) <> 0 then
substring(account from 1 for 10) else
if(cast(substring(account from 2 for 10) as integer) <> 0 then
substring(account from 2 for 10) else
if(cast(substring(account from 3 for 10) as integer) <> 0 then
substring(account from 3 for 10) else
if(cast(substring(account from 4 for 10) as integer) <> 0 then
substring(account from 4 for 10) else
if(cast(substring(account from 5 for 10) as integer) <> 0 then
substring(account from 5 for 10) else
if(cast(substring(account from 6 for 10) as integer) <> 0 then
substring(account from 6 for 10) else
if(cast(substring(account from 7 for 10) as integer) <> 0 then
substring(account from 7 for 10) else
if(cast(substring(account from 8 for 10) as integer) <> 0 then
substring(account from 8 for 10) else
if(cast(substring(account from 9 for 10) as integer) <> 0 then
substring(account from 9 for 10) else
if(cast(substring(account from 10 for 10) as integer) <> 0 then
substring(account from 10 for 10) else
'')))))))))) from table

John

Thats using your Nogin.! Well done.

"John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> wrote in
message news:52864FAA-F928-4940-8992-87DADA3DE525@news.elevatesoft.com...
> Adam
>> I know it's a longshot, but I'm wondering if their is a way in SQL to
>> pull
>> out just the numeric fields from a string column.
>>
>> Ie - if the field is ABC123, I want the result to only be 123.
>>
>> If even more possible, if the result was 12ABC123, I would prefer the
> result
>> to be just 123, but if that's not possible, but I could get the result
>> 12123 - that would suffice.
>
> I know its a bit contrived but something like the following (assuming
> column
> length of 10)  should do what you want
>
> select
> if(cast(substring(account from 1 for 10) as integer) <> 0 then
> substring(account from 1 for 10) else
> if(cast(substring(account from 2 for 10) as integer) <> 0 then
> substring(account from 2 for 10) else
> if(cast(substring(account from 3 for 10) as integer) <> 0 then
> substring(account from 3 for 10) else
> if(cast(substring(account from 4 for 10) as integer) <> 0 then
> substring(account from 4 for 10) else
> if(cast(substring(account from 5 for 10) as integer) <> 0 then
> substring(account from 5 for 10) else
> if(cast(substring(account from 6 for 10) as integer) <> 0 then
> substring(account from 6 for 10) else
> if(cast(substring(account from 7 for 10) as integer) <> 0 then
> substring(account from 7 for 10) else
> if(cast(substring(account from 8 for 10) as integer) <> 0 then
> substring(account from 8 for 10) else
> if(cast(substring(account from 9 for 10) as integer) <> 0 then
> substring(account from 9 for 10) else
> if(cast(substring(account from 10 for 10) as integer) <> 0 then
> substring(account from 10 for 10) else
> '')))))))))) from table
>
> John
>
>

Hey John,

I'm impressed! Thanks for showing me that. However, under the circumstances,
I think that it may be a little more beneficial to use custom functions.
Still - I must admit, it's nice to know it can be done purely in SQL.  

Cheers

Adam.