Find a string of a specific format within a field

[datanibbler]

Hi,

how can I search in a field for a string constituting an order_number which I need - to split it out and then see if the rest is usable.

The format is

>> S######## << (the letter S and 8 numbers)

There may be no BLANK inbetween or it might be something else - just this string.

It is in a field together with a lot of other stuff, the whole looks like this

SN 8638294 / 4 Stück / S80140915

Can anyone tell me how I could do this?

Thanks a lot!

Best regards,

DataNibbler

[its_anandrjs]

Use Index function for this

From QV Help

index(s1 , s2[ , n])

Position of a substring. This function gives the starting position of the n:th occurrence of substring s2 in string s1. If n is omitted, the first occurrence is assumed. If n is negative, the search is made starting from the end of string s1. The result is an integer. The positions in the string are numbered from 1 and up.

Examples:

index( 'abcdefg', 'cd' ) returns 3

index( 'abcdabcd', 'b', 2 ) returns 6

index( 'abcdabcd', 'b', -2 ) returns 2

[its_anandrjs]

And try like

Load

If(Index(FieldName,'S########'),1,0) as FieldFlag

From Location;

If having any sample file then provide that one.

Regards,

Anand

[ashfaq_haseeb]

Hi

try with ? instead

S????????

Regards

ASHFAQ

[datanibbler]

Hi all,

what is the Wildcard for one numeric character?

I guess I could use that to specify the format of the string I am looking for.

I have to split that out of the field and see what remains and how I can process that.

[datanibbler]

Hi,

if I have found a string matching that format - it begins with the letter S - how can I find out whether this is the 1st, 2nd or 3rd iteration of the letter S - so that I can cut that out of the field?

In the field I am currently working on to test, it is the 3rd - but near everything in that field is unsure - it might be the 1st occurence of that letter.

Thanks a lot!

Best regards,

DataNibbler

[tresesco]

May be like:

Load
String,
SeparatedString,
If(Left(SeparatedString,1)='S',If(IsNum(Mid(SeparatedString,2)) and Len(SeparatedString)=9, SeparatedString)) as Found;

Load String,
  Trim(SubField(String, '/')) as SeparatedString Inline [
String
'SN 8638294 / 4 Stück / S80140915']

PFA

[its_anandrjs]

Please share any sample file my concern is if your string is in between the string as you say need to search what is the iteration 1st or 2nd.

Regards,

Anand

[datanibbler]

Hi tresesco,

that might help.

I guess SeparatedString is not a function, but just a term that you use.

Still - I can find out if there is a piece consisting of a letter S, followed by 8 digits of any kind - there is no wildcard for numerics that won't find alphanumeric characters, is there?

I can then check if the next digit after that is numeric - that would rule out BLANKs and all kinds of separators - and then cut it out.

I'll try that.

[tresesco · Accepted solution]

No such function. Try looping through the string like:

DataNibbler wrote:

Load
String,
If(Mid(String,iterno(),1)='S',If(IsNum(Mid(String,IterNo()+1,IterNo()+8)), Mid(String,IterNo(),IterNo()+9))) as Found

While iterno()<=Len(String);

Load * Inline [
String
'SN 8638294 / 4 Stück / S80140915']

PFA