Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
LOAD
...
'[#' & FIELD & ']' as FIELD,
...
from Table1;
edit: Have you tried the purgechar() ?
use
subfield(fieldname,'#',2)
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
Sunil,
That one is close but it gets me ODI-1].
Hi,
Try this
keepchar ( '#[ODI-10]','ODI-0123456789' )
Celambarasan
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)
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.
Hi,
Then you ve to use either
purgechar('[#ODI-1]','[]#')
or
keepchar ( '#[ODI-10]','ABCDEFGHIJKLMNOPQRSTUVWXYZ-0123456789' )
or
replace(replace('[#ODI-01]' , '[#' , '') , ']' , '')
Celambarasan
may be this u want
subfield(subfield(fieldname,'#',2) ,'-',1)
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.