Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In sql SUBSTR(ACCOUNT_NO,-7) will give the last 7 digits.
Ex: 276-76288330 will become 6288330
I tried right(ACCOUNT_NO,7)
It is not working?Is there anything else I can try ?
Hi, That should works, dont forget to do right(ACCOUNT_NO,7) as ACCOUNT_NO.
//Staffan
Hi, That should works, dont forget to do right(ACCOUNT_NO,7) as ACCOUNT_NO.
//Staffan
right('276-76288330',7)
should work, returns 6288330 to me.
To be save you should use:
right(text(ACCOUNT_NO), 7)
- Ralf
Function right() works:
right('276-76288330',7) returns 6288330
Can you show a non-working example?
If(index(ACCOUNT_NO, '-') =0,
ACCOUNT_NO,
right(ACCOUNT_NO, (index(ACCOUNT_NO, '-', -1) -1) )
) as ACCOUNT_NO
This tests if the account no contains '-', if not then the account no is used as is. If '-' is present, the second index command uses the -1 parameter to find the position of the dash counting from the end of the string, subtracts one from this figure, then uses this for the right command to extract the account number.
This will work for account number strings that have the dash in varying positions.
Text(Right(Text,CNT))
Hi
If you want to extract de number after the '-' you can use de subfields() function
Example:
LOAD '1234-12341243-Field3' as text,
SubField('1234-12341243-Field3', '-', 1) as f1,
SubField('1234-12341243-Field3', '-', 2) as f2,
SubField('1234-12341243-Field3', '-', 3) as f3
autogenerate 1;
you get:
f1 = 1234
f2 =12341234
f3 = Field3
Regards