Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I am having an issue with my syntax and was wondering if someone could help me. I only want the first 13 positions. What am I doing wrong? Thanks
if(len(Address2_Telephone1) >= 12,
(Mid(Replace(Replace(Replace(Address2_Telephone1,' ',''),Chr(10),' '),Chr(13),' '),1,13))) as New_Address2_Telephone1,
May be this
left (keepchar (Address2_Telephone1
,'0123456789 ()-'),13) as New_Address2_Telephone1
Would be best if you can share 2-3 data lines. Also I notice you don't have a false condition?
if(len(Address2_Telephone1) >= 12,
(Mid(Replace(Replace(Replace(Address2_Telephone1,' ',''),Chr(10),' '),Chr(13),' '),1,13)), Address2_Telephone1) as New_Address2_Telephone1,
Maybe you also want to review your order of replace statements, maybe the replace of ' ' (space) with an empty string '' should be last when executing inner to outer Replace functions.
You may also want to consider using PurgeChar() function.
the above syntax is giving me more than 13 positions in my output row. Example: (330)876-5412x998 I only want the first 13 positions (330)876-5412. Hope this helps.
What if you do:
Left(Address2_Telephone1, 13) as Address2_Telephone1
Your original script seems to work for me:
LOAD *,
if(len(Address2_Telephone1) >= 12,
(Mid(Replace(Replace(Replace(Address2_Telephone1,' ',''),Chr(10),' '),Chr(13),' '),1,13))) as New_Address2_Telephone1;
LOAD * INLINE [
Address2_Telephone1
(330)876-5412x998
];
I don't want the x998 at the end. I only want this (330)876-5412
This isn't working?
Left(Address2_Telephone1, 13) as Address2_Telephone1
May be this
left (keepchar (Address2_Telephone1
,'0123456789 ()-'),13) as New_Address2_Telephone1
I think if he does this, then may be just 10?
left (keepchar (Address2_Telephone1
,'0123456789 ()-'),10) as New_Address2_Telephone1