Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Not applicable
Author

thanks everyone for helping

final solution:

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

View solution in original post

6 Replies
swuehl
MVP
MVP

Maybe like

LOAD

     Field1,

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

     ...

FROM YourTable;

tresesco
MVP
MVP

Try like:

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

Not applicable
Author

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

Not applicable
Author

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
Specialist
Specialist

Not particularly elegant but:

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

Would work.

Not applicable
Author

thanks everyone for helping

final solution:

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