-1

I have a table where the data looks like below:

col1| col2  | col3
101 | AA AB | AAAA
102 | ACA B | AAAA
103 | ACBA  | AAAA
104 | ABCD  | AAAA

What I am trying and need is, select all the records that have a space in col2, from the above example, my select should return both 101 and 102. The space isn't a fixed field, it can be in any character.

Select * from tableA where col2 like '% %'

this does not help me as the '%' will start fetching all records, if I have to use a function substring then my space needs to at a fixed position, which is not in this case.

any suggestions, please

user3040077
  • 71
  • 2
  • 10

2 Answers2

2

Try this

 select * 
   from tableA 
  where col2 like '%_ _%'

use '%_ _%' mask instead of '% %' to filter out ' ABCD' as well as 'ABCD '

Edit: It may happen, that your data have many trailing spaces, e.g. 'ABCD '; in this case try to trim the data:

  select * 
       from tableA 
      where trim(col2) like '% %'
Dmitry Bychenko
  • 180,369
  • 20
  • 160
  • 215
  • Hi Dmitry; '%_ _%' does not work, I am still getting all records back, if you meant to suggest have some character instead of '_' then that's not an option since the col2 data need not necessarily always start with A. – user3040077 May 12 '14 at 14:25
  • 1
    @user3040077: it looks that your data have trailing spaces, e.g. 'ABCD '; try trimming it (see my edit) – Dmitry Bychenko May 12 '14 at 15:41
0

On the W3C website, you can try out SQL code. I tried with your '% %' syntax and it returned the expected result.

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_wildcard_percent

Tom Cools
  • 1,098
  • 8
  • 23
  • '%' is used as a wild character, so if I have it in my select SQL (such as col2 like '% %') what it means is, its going to select all the records where there is something in col2, coz the wild character will consider any value. '%%' is not working because for record 103 it would be %%'= %= ACBA and end it would consider space and again wildcharacter which is nothing. – user3040077 May 12 '14 at 14:21