9 Replies Latest reply: Apr 29, 2014 10:11 AM by RobbR RSS

    query to find data having leading, trailing spaces.

    816629

      Hi Experts

      I am looking for sql queries for oracle database for below conditions

      1. query to display  data in a column of  a table having leading spaces

       

      2. query to display  data in a column of  a table having trailing spaces

        • 1. Re: query to find data having leading, trailing spaces.
          Frank Kulash

          Hi,

           

          For leading spaces:

           

          SELECT  str

          FROM     table_x

          WHERE   SUBSTR (str, 1, 1)  = ' '

          ;

          For trailing spaces, change the WHERE clause to

           

          WHERE   SUBSTR (str, -1)  = ' '

           

          You could also use

           

          WHERE   str  LIKE ' %'

          for leading spaces and

           

          WHERE   str  LIKE '% '

          for trailing spaces.

          • 2. Re: query to find data having leading, trailing spaces.
            Jjun.Tan

            Hi,

             

            SELECT * FROM Table1 WHERE (col1 LIKE ' %') OR (col1 LIKE '% ');

             

            Reference: LIKE and Oracle/PLSQL: LIKE Condition

             

            Warmest Regards,

            Jjun

            • 3. Re: query to find data having leading, trailing spaces.
              816629

              How about if there are more spaces as leading or trailing, i am sure to will below query works

               

              trailing space

              Select * From TableA Where MyColumn <> TRIM(MyColumn)

               

              leading space

              i think we have to use REVER function.

               

              please advice

              • 4. Re: query to find data having leading, trailing spaces.
                Frank Kulash

                Hi,

                 

                 

                816629 wrote:

                 

                How about if there are more spaces as leading or trailing, i am sure to will below query works

                 

                trailing space

                Select * From TableA Where MyColumn <> TRIM(MyColumn)

                 

                leading space

                ...

                 

                "Leading spaces" means any number of spaces, 1 or more.  There is no distinction between the special case of have exactly 1 space, and having 2 or more.

                If you nead to do somehing different depending on the number of spaces, what is it?  Post a little sample data (CREATE TABLE and INSERT statements) and the exact results you want from that data.

                See the forum FAQ: https://forums.oracle.com/message/9362002

                 

                Be careful when using TRIM (or LTRIM or RTRIM).  If MyColumn contains only spaces, then any of those functions will return NULL, and  MyColumn <> TRIM (MyColumn) will not be TRUE.

                i think we have to use REVER function.

                 

                please advice

                There is no REVER function in Oracle.  Do you mean a user-defined function you have?  If so, post it.

                • 5. Re: query to find data having leading, trailing spaces.
                  brunovroman

                  Very good remark of Frank.

                   

                  "Be careful when using TRIM (or LTRIM or RTRIM).  If MyColumn contains only spaces, then any of those functions will return NULL, and  MyColumn <> TRIM (MyColumn) will not be TRUE."

                   

                  Using a "workaround" to be sure that we never compare to NULL: concatenate a not null string to the field...

                   

                  WITH mytable AS

                  ( SELECT 'abc' myfield FROM dual

                  UNION ALL SELECT ' def' FROM dual

                  UNION ALL SELECT 'ghi  ' FROM dual

                  UNION ALL SELECT ' jkl  ' FROM dual

                  UNION ALL SELECT ' ' FROM dual

                  UNION ALL SELECT NULL FROM dual

                  )

                  SELECT CASE WHEN LTRIM( myfield ) || 'x' != myfield || 'x'

                               AND RTRIM( myfield ) || 'x' != myfield || 'x'

                              THEN 'both leading and trailing spaces'

                              WHEN LTRIM( myfield ) || 'x' != myfield || 'x'

                              THEN 'leading spaces'

                              ELSE /* or, to make things clear: WHEN RTRIM( myfield ) || 'x' != myfield || 'x'  THEN */

                                   'trailing spaces'

                         END spaces

                       , '<' || myfield || '>'

                    FROM mytable

                    WHERE TRIM( myfield ) || 'x' != myfield || 'x'

                  ;

                  SPACES                           '<'||MYF

                  -------------------------------- --------

                  leading spaces                   < def>

                  trailing spaces                  <ghi  >

                  both leading and trailing spaces < jkl  >
                  both leading and trailing spaces <  >

                   

                  Best regards,

                   

                  Bruno Vroman.

                  • 6. Re: query to find data having leading, trailing spaces.
                    RobbR

                    You can always use regexp_like ....

                     

                    Leading space:

                    with tbl as

                    (

                      select ' Test' a from dual

                      union

                      select 'Test ' a from dual

                      union

                      select ' ' a from dual

                    )

                    select * from tbl

                    where regexp_like(a, '^[[:blank:]]+[[:alnum:]]+');

                     

                    Trailing space:

                    with tbl as

                    (

                      select ' Test' a from dual

                      union

                      select 'Test ' a from dual

                      union

                      select ' ' a from dual

                    )

                    select * from tbl

                    where regexp_like(a, '[[:alnum:]]+[[:blank:]]+$');

                    • 7. Re: query to find data having leading, trailing spaces.
                      brunovroman

                      Hi RobbR,

                       

                      WITH mytable AS

                      ( SELECT 'abc' myfield FROM dual

                      UNION ALL SELECT ' def' FROM dual

                      UNION ALL SELECT 'ghi  ' FROM dual

                      UNION ALL SELECT ' jkl  ' FROM dual

                      UNION ALL SELECT ' ' FROM dual

                      UNION ALL SELECT NULL FROM dual

                      )

                      select * from mytable

                          where regexp_like( myfield, '[[:alnum:]]+[[:blank:]]+$')

                      ;

                      MYFIEL

                      ------

                      ghi

                      jkl

                       

                      Hmmmm...

                       

                      Bruno

                      • 8. Re: query to find data having leading, trailing spaces.
                        GregV

                        I'd use the following pattern instead for regexp_like:

                        regexp_like(myfield, '(^[[:blank:]])|([[:blank:]]$)')

                         

                        But here there's no need to use regexp_like, I would prefer, as Frank has shown, using LIKE '% ' and LIKE ' %'.

                        • 9. Re: query to find data having leading, trailing spaces.
                          RobbR

                          brunovroman wrote:

                           

                          Hi RobbR,

                           

                          WITH mytable AS

                          ( SELECT 'abc' myfield FROM dual

                          UNION ALL SELECT ' def' FROM dual

                          UNION ALL SELECT 'ghi  ' FROM dual

                          UNION ALL SELECT ' jkl  ' FROM dual

                          UNION ALL SELECT ' ' FROM dual

                          UNION ALL SELECT NULL FROM dual

                          )

                          select * from mytable

                              where regexp_like( myfield, '[[:alnum:]]+[[:blank:]]+$')

                          ;

                          MYFIEL

                          ------

                          ghi

                          jkl

                           

                          Hmmmm...

                           

                          Bruno

                           

                          I see what you did there, sorry was a little confused....