parsing string

[#1]

hi all,

i am wondering if it is possible to read specific digits from a string during the load.

the pattern of the string is always the same - means:

i want to read all numbers from the first - (if followed by a number) up to the first .

example:

somestringheredoesntsaymuch556-areacode12345-newyork-8797.3652

in this string i would want to extract 8797

what would be the proper way to doe this during the script load ?

thanks a lot.

k

[kaushiknsolanki]

Hi,

Try this.

Tables:

LOAD * INLINE [
    TablesName
    somestringheredoesntsaymuch556-areacode12345-newyork-8797.3652

];

data:
Load mid(TablesName,index( TablesName, '-', 3 )+1,4)  as abc
Resident Tables;

Regards,

Kaushik Solanki

[#3]

Thanks, but this does not do the job for all strings i have.

The number to extract does not always start after the 3rd -

Sometimes after the 2nd or after the 4th -

But the right - is ALWAYS followed by a number.

Your approach would f.e. not work for something like this:

some-stringheredoesntsaymuch556-areacode12345-newyork-8797.3652

Can you help ?

[vijay_iitkgp]

Hi,

If it is always right number then you can use following. Use fieldname instead of value below

=Subfield('some-stringheredoesntsaymuch556-areacode12345-newyork-8797.3652','-',Substringcount('some-stringheredoesntsaymuch556-areacode12345-newyork-8797.3652','-')+1)

[Jason_Michaelides]

Or I think -1 in SubField() gets the last one, so:

Floor(SubField([YourStringField],'-',-1))

Floor() gets the integer part of the number, so 8797 from 8797.3652

SubField() breaks up the string using '-' as the delimiter and -1 gets the last substring.

Hope this helps,

Jason

[#6 · Accepted solution]

I have done it this way:

Tables:

Load

          TablesName,

          if (Chunk = 'No ID', Chunk, TextBetween(Chunk, '-', '.') )                                                                                          as ID

;

Load

TablesName,

          if (IsNum( mid(TablesName, index(TablesName, '-', 3)+1,1) ), mid(TablesName,index( TablesName, '-', 3 )), 'No ID'          )          as Chunk

;

thanks!

[Jason_Michaelides]

Well as long as it works! Your solution seems a little over-engineered, and seems to count on a certain number/position of the hyphens, which I thought you couldn't do.

Glad you sorted it.