Get part of a string

[#1]

Hi Everyone,

I have two fields that mean the same from 2 different tables, but are not the same in characters.

The first one is like ODI-1, the second one is like [#ODI-1].

I have tried mid(field, 3, 5) and that works for now. But what do to when we reach [#ODI-10]  until [#ODI-99] or higher?

Or is there maybe another way to connect these fields because they mean the same?

[SunilChauhan]

use

subfield(fieldname,'#',2) 

[swuehl]

You could also look into purgechar if the difference in table 1 and table 2 are characters that table 2 has, but table1 doesn't use.

like

purgechar('[#ODI-1]','[]#') as LINKFIELD,

Regards,

Stefan

[#4]

Sunil,

That one is close but it gets me ODI-1].

[CELAMBARASAN]

Hi,

     Try this

          keepchar ( '#[ODI-10]','ODI-0123456789' )

Celambarasan

[#6]

Hi Celambarasan,

What would I do with that?

ODI-1 was just an example. It can be any combination of 3 letters with a - and a number up to 999 (in theory)

[swuehl]

If your second table field just shows some defined additional characters, use purgechar

purgechar( FIELDNAME, '[]#' ) as FIELDNAME,

if your additional characters are undefined, but your characters you want to keep are known and a smaller set, you could use keepchar (just add all letters a to z to above literal, second parameter to keepchar). In both cases you would need to know about the possible character sets in both fields.

[CELAMBARASAN]

Hi,

     Then you ve to use either

                              purgechar('[#ODI-1]','[]#')

                                        or

                              keepchar ( '#[ODI-10]','ABCDEFGHIJKLMNOPQRSTUVWXYZ-0123456789' )

                                        or

                              replace(replace('[#ODI-01]' , '[#' , '') , ']' , '')

Celambarasan

[SunilChauhan]

may be this u want

subfield(subfield(fieldname,'#',2) ,'-',1)

[#10]

Thanks swuehl,

The second table can contain any kind of text, it's a remark field. But when it has the same meaning as the first table, the field only contains a value like [#abc-1] or [#def-2]. So, the first two characters are always [# but the rest can differ from xxx-1 until xxx-9999. And that is what I want to see.