6 Replies Latest reply: Jun 26, 2015 6:25 AM by laura howarth

# Return everything before number in a string

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

• ###### Re: Return everything before number in a string

Maybe like

Field1,

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

...

FROM YourTable;

• ###### Re: Return everything before number in a string

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

• ###### Re: Return everything before number in a string

Try like:

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

• ###### Re: Return everything before number in a string

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

• ###### Re: Return everything before number in a string

Not particularly elegant but:

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

Would work.

• ###### Re: Return everything before number in a string

thanks everyone for helping

final solution:

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