Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try like:
Load
If(Index(Designation, '48'), 48) as Code48
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.
another one:
Pick(WildMatch(Designation,'*48*'),48) as Code48
If(Designation Like '%48%'), 48) as Code48
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.
Very helpful