ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

trimming field value

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • trimming field value

    Hi,


    How to trim a field value in SQL query in such a way so that we could just remove extra two characters coming in this field at the end.



    Thanks

  • #2
    Several TRIM , and other useful string functions

    https://www.ibm.com/docs/en/psfa/7.2...ring-functions
    Last edited by MFisher; June 23, 2022, 09:44 AM.

    Comment


    • #3
      so it appears I need to use rtrim(s,t) here .

      I tried it like :- select rtrim(fieldname,2) from file name but output I got is it's field heading got changed with RTRIM and I could not see any impact in fields values , I wanted to remove field's last 2 character here but it did not happen when I used this rtrim like this.


      thanks

      Comment


      • #4
        It would help if you post a variety of sample data, and the desired results.

        Comment


        • #5
          example

          fileld1
          123456XY


          so like here i want this XY (last two characters to be removed.

          so that output should look like below:-

          field1
          123456



          Thanks

          Comment


          • #6
            It should be pointed out that the IBM link is for the Netezza product. "The following table describes the IBM® Netezza® functions for manipulating character strings."

            Comment


            • #7
              I even tried below link which is for IBM® Netezza® functions but not sure how to remove trailing extra two characters in my mentioned example.





              Thanks

              Comment


              • #8
                Originally posted by john192 View Post
                example

                fileld1
                123456xy


                so like here i want this xy (last two characters to be removed.

                So that output should look like below:-

                field1
                123456



                thanks
                Code:
                SUBSTR(field1,1,6)

                Comment


                • #9
                  Originally posted by kmonahan01 View Post
                  It should be pointed out that the IBM link is for the Netezza product. "The following table describes the IBM® Netezza® functions for manipulating character strings."
                  I find it difficult and frustrating to find the correct, adequate documentation from IBM.
                  I tried finding V7 R4 version and gave up after a bit, because I am busy with other things.

                  Comment


                  • #10
                    ok, if there is join of two files ( on fields with different data types and length)
                    Code:
                     CREATE VIEW QTEMP/FILE3
                    >    ( FLD1 , FLD2 , FLD3 , FLD4 ) AS
                    >    ( SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2
                    >      FROM QTEMP/FILE1 a
                    >      JOIN QTEMP/FILE2 b
                    >        ON DIGITS(A.FLD1) = CAST(TRIM(B.FLD1) AS CHAR(6))
                    >    )
                    FLD1 of file1 is of 6 (Decimal) and FLD1 of file2 of 9 character and we are looking a match on these fields from file1's field1 ( it has some 6 digit numbers) to file2' field1 ( which too have these 6 digit number but it's position keep on varying in this field ( not fixed position but could be between 1st to 4th position as total length of this number is 6 and field's length is 9 here)

                    so i tried above join but it's giving results like below:-

                    FLD1 FLD1
                    1234 001234
                    1234 001234XY


                    so in order to remove these extra characters XY here in this case how should SUBSTR be used here in this SQL Query?


                    Thanks

                    Comment


                    • #11
                      The IBM i 7.4 SQL documentation is here: https://www.ibm.com/docs/en/i/7.4?topic=reference-sql

                      Firstly, this line:

                      ON DIGITS(A.FLD1) = CAST(TRIM(B.FLD1) AS CHAR(6))

                      You should avoid doing a transform on both sides of the equals in a logical comparison in SQL, as it means the SQL engine cannot use keyed lookups and must check every record in the table(s). In your example, you should convert B.FLD1 to numeric so it can be directly compared to A.FLD1 so A.FLD1 does not need to be transformed. Then FILE1 can use keyed lookups if an index is available.


                      Secondly, it doesn't seem like you want to trim XY form the end, since as you said, the numeric values can appear anywhere inside the field. So you could have X001234XY ?

                      What you actually want is to extract the numeric portion of the field. If you can guarantee that there is only a single block of numbers (i.e. not X12B34C where you only want the 12, not 1234), then you can use REGEXP_REPLACE: https://www.ibm.com/docs/en/i/7.4?to...regexp-replace

                      REGEXP_REPLACE lets you find and replace characters in a string using Regex pattern matching. This will take the string B.FLD1, and replace all characters that are not numbers (0-9) with an empty string, removing them. Then you can wrap that in INT() to convert to a numeric that can be directly compared with A.FLD1:

                      on A.FLD1 = INT(REGEXP_REPLACE(B.FLD1,'[^0-9]',''))

                      Note this only works if you can guarantee that every B.FLD1 contains at least one numeric character. If not, REGEXP_REPLACE returns an empty string, and int() will error trying to convert it. To solve that, you can wrap REGEXP_REPLACE with NULLIF() https://www.ibm.com/docs/en/i/7.4?to...nctions-nullif
                      NULLIF(a,b) returns a if a is not equal to b, else it returns null. So you can convert as blank return from REGEXP_REPLACE to null, which is passed through INT(), resulting in the comparison safely returning false


                      on A.FLD1 = INT(NULLIF(REGEXP_REPLACE(B.FLD1,'[^0-9]',''),''))

                      Comment


                      • #12
                        i am getting syntax error like 'token <end of statement> was not valid valid tokens 0 ,

                        i tried this
                        CREATE VIEW QTEMP/FILE3 > ( FLD1 , FLD2 , FLD3 , FLD4 ) AS > ( SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2 > FROM QTEMP/FILE1 a > JOIN QTEMP/FILE2 b > ON on A.FLD1 = INT(NULLIF(REGEXP_REPLACE(B.FLD1,'[^0-9]',''),''))

                        Thanks

                        Comment


                        • John192
                          John192 commented
                          Editing a comment
                          tried 'CREATE VIEW QTEMP/FILE3 > ( FLD1 , FLD2 , FLD3 , FLD4 ) AS > ( SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2 > FROM QTEMP/FILE1 a > JOIN QTEMP/FILE2 b > ON A.FLD1 = INT(NULLIF(REGEXP_REPLACE(B.FLD1,'[^0-9]',''),''))


                          got same error -'syntax error like 'token <end of statement> was not valid valid tokens 0 ,'


                          Thanks

                      • #13
                        Originally posted by John192 View Post

                        FLD1 of file1 is of 6 (Decimal) and FLD1 of file2 of 9 character and we are looking a match on these fields from file1's field1 ( it has some 6 digit numbers) to file2' field1 ( which too have these 6 digit number but it's position keep on varying in this field ( not fixed position but could be between 1st to 4th position as total length of this number is 6 and field's length is 9 here)

                        It took you until post # 10 to mention you want to extract digits from varying positions.
                        Twice you said you want to remove the last 2 characters, and gave a single example of 123456XY
                        If you clearly explain things in your first post, you will get better answers, and cause less frustration .
                        It's unreasonable to expect people to spend their time trying to solve poorly explained problems.
                        Last edited by MFisher; June 24, 2022, 06:34 AM.

                        Comment


                        • #14
                          I was trying to resolve it using SUBSTR and TRIM and when it did not work then explained it in detail but still getting that error- error -'syntax error like 'token <end of statement> was not valid valid tokens 0 ,'


                          Thanks

                          Comment


                          • #15
                            Originally posted by John192 View Post
                            I was trying to resolve it using SUBSTR and TRIM and when it did not work then explained it in detail but still getting that error- error -'syntax error like 'token <end of statement> was not valid valid tokens 0 ,'


                            Thanks
                            If you explain it correctly at the beginning in post # 1 nobody would waste time giving you a suggestion that doesn't work.
                            Minimalism is a valid concept in art. Not so much for explaining programming problems.

                            Comment

                            Working...
                            X