are there tricks to matching consecutive white space characters using Like and wild-cards

General
Enthusiast

are there tricks to matching consecutive white space characters using Like and wild-cards

I have a perplpexing issue, which seems to me must have a simple answer -

I am attempting to natch values  of  a column that contain embedded whte space of more than one consecutive <space> character

In other words if I have two character strings ,  

'ABC DEF'  ( 6 characters bisected by a single whte space )

'GHI  JKL'  ( 6 characters bisected by 2 consecutive  white spaces )

I issue this statement 

select * from table where col1 like '%  %'   -- wild card preceding two consecutive whie spaces, followed by wildcard 

I expect to get only 1 row back :   'GHI  JKL'.

However,  this returns not only the string containing 2 consecutive white spaces, but also the string containing only one white space.

My question is 1:  why? 

2.  Is there something basic that I am overlooking that would allow me to match only the column(s) which contain multiple consecutive white space characters embedded in a character string?

1 REPLY
Enthusiast

Re: are there tricks to matching consecutive white space characters using Like and wild-cards

I apologize,  my query for column values that match '%  %' was being deceived by trailing whitespace in a column of CHAR(30).  any content less than 29 characters would match...  ...my bad.