Select only some number with keepchar

[louisernould]

Hi everybody,

I have a very big column with letter & numbers and I want to create a second column with only some numbers of the first one.

for exemple This is the table  I want

Designation	Code48
AAA 48 BA 128	48
AA14	-
BBB48A214	48

So I tried with the Keepchar function:

Load

     Designation

      KeepChar(Designation, 48) as code48

From Table

But the problem is, when I try that code, I have this table

Designation	Code48
AAA 48 BA 128	488
AA14	4
BBB48A214	484

Does someone have an idea ? It's the end for me ^^

Thanks,

Louis

[tresesco · Accepted solution]

Try like:

Load

          If(Index(Designation, '48'), 48) as Code48

[louisernould]

YOU ARE MY GOD !

Thank you very much it work !! And moreover it was fast ^^

Tresesco winner !

[martynlloyd]

If you want a robust method, to extract the first group of numbers (variable len), without using a loop, try something like

input:
Load * Inline [
desig
AAA 48 BA 128
AA14
CC24
BBB48A214
];

temp1:
LOAD
PurgeChar(desig,' ') AS desig,
FindOneOf(PurgeChar(desig,' ') , '0123456789') as start,
Mid(PurgeChar(desig,' '),FindOneOf(PurgeChar(desig,' ') , '0123456789'),len(PurgeChar(desig,' '))) as string2,
FindOneOf(Mid(PurgeChar(desig,' '),FindOneOf(PurgeChar(desig,' ') , '0123456789'),len(PurgeChar(desig,' '))) , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
-1 as length

Resident input
;

Result:
LOAD
desig,
f(length<=0, mid(desig,start,len(desig) -(start-1)), mid(desig, start, length)) as code
Resident temp1
;

drop Table input, temp1;

regards,

M.

[MarcoWedel]

another one:

Pick(WildMatch(Designation,'*48*'),48) as Code48

[anbu1984]

If(Designation Like '%48%'), 48) as Code48

[louisernould]

actually, all your answers are correct that's perfect !

Martyn Lloyd, yours is quite hard, but much robust. I needed only a simple solution. But thks anyway, I like the logic of it.

[#8]

Very helpful