Return everything before number in a string

[#1]

Hi,

i have an alphanumeric field where i want to return everything left and including the first number.

For example.

Field1

Java 3 patch 1

Java 6 version

Java patch 3

Java 7 version

Java 7 (TM) 123

i want to return

Field1

Java 3

Java 6

Java patch 3

Java 7

Java 7

i think i need to use the left and the subfield functions but i don't know how to say return anything left of the first number you find.

Any help would be great.

thanks

[swuehl]

Maybe like

LOAD

     Field1,

     Left( Field1, FindOneOf( Field1,'0123456789') ) as LeftPart,

     ...

FROM YourTable;

[tresesco]

Try like:

=Left(Field1,FindOneOf(Field1, '0123456789'))

[#4]

Hi ,

This works but if there is a is no number it returns a blank.

Is there a way to say if there is no number to return the string itself

E.G

Field1

Java 3 patch 1

Java patch

Java 6 version

Java patch 3

java

Java 7 version

Java 7 (TM) 123

i want to return

Field1

Java 3

java patch

Java 6

Java patch 3

java

Java 7

Java 7

[#5]

Hi ,

This works but if there is a is no number it returns a blank.

Is there a way to say if there is no number to return the string itself

E.G

Field1

Java 3 patch 1

Java patch

Java 6 version

Java patch 3

java

Java 7 version

Java 7 (TM) 123

i want to return

Field1

Java 3

java patch

Java 6

Java patch 3

java

Java 7

Java 7

[morganaaron]

Not particularly elegant but:

=Left(Field1, If(FindOneOf(Field1, '0123456789') > 0, FindOneOf(Field1, '0123456789'), Len(Field1)))

Would work.

[#7 · Accepted solution]

thanks everyone for helping

final solution:

if(FindOneOf(field1,'1234567890')=0, field1, (left(field1, FindOneOf(field1,'1234567890'))))