Syntax Problem

[tmumaw]

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,

[sunny_talwar]

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,

[swuehl]

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.

[tmumaw]

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.

[sunny_talwar]

What if you do:

Left(Address2_Telephone1, 13) as Address2_Telephone1

[swuehl]

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

];

[tmumaw]

I don't want the x998 at the end.  I only want this (330)876-5412

[sunny_talwar]

This isn't working?

Left(Address2_Telephone1, 13) as Address2_Telephone1

[settu_periasamy · Accepted solution]

May be this

left (keepchar (Address2_Telephone1

,'0123456789 ()-'),13) as New_Address2_Telephone1

[sunny_talwar]

I think if he does this, then may be just 10?

left (keepchar (Address2_Telephone1

,'0123456789 ()-'),10) as New_Address2_Telephone1