6 Replies Latest reply: Nov 12, 2013 2:26 AM by Steve Dark RSS

    Trim Field Values

    Hardik Gandhi

      Hi,

       

      I need to trim some field values in a product table. All products are having their name starting with 'PR'.

       

      Example (PRODUCT TABLE): PRFOOD, PRDRINKS, PDFLAVORS etc

       

      I need to remove 'PR' from front of all above names while loading the script.

       

      Regards,

      H

        • Re: Trim Field Values
          Steve Dark

          Hi,

           

          You simply need to alias the field names using the AS statement, eg:

           

          LOAD
            PRFOOD as Food,

             PRDRINKS as Drinks,

             PRFLAVORS as Flavors,

           

          When I have many many fields that I have to do this sort of thing for I tend to paste the field list into Excel then in an adjoining cell have an expression to build the load script for me, eg:

           

          =CONCATENATE("     ", A1, " AS [", PROPER(MID(A1, 3, 99)), "],")

           

          This can then be copied down for each field in Excel, the column containing all the values can then be copied and pasted directly into your load script.

           

          Hope that helps,

          Steve

          • Re: Trim Field Values
            Ian McGivern

            Hiya,

             

            Another option could be:

             

            Load
                
            ProductID
                 ,
            SubField(ProductName,'PR',2)     AS NewProductName;

            SQL SELECT
                  Product ID
                 , ProductName
            FROM ProductsTable;

             

            Hope this helps.