February 17, 2004 at 9:50 am
I am trying to find records in a field that contain blank spaces. Basically blank spaces in a string. I have tried using the CONTAINS function and the LIKE operator with no success.
CONTAINS(field1, '" "') produces nothing but a complaint from SQL Server
Like '% %' finds everything of course as I feared it would.
Anyone ever done this successfully?
Thanks for any help you can provide
February 17, 2004 at 11:01 am
create table test
(field1 char(5))
insert into test
values('abcde')
insert into test
values('ab de')
insert into test
values('ab d ')
select * from test
where charindex(' ',field1) > 0
two results:
ab de
ab d
Michelle
February 17, 2004 at 11:58 am
Thank you Michelle
February 17, 2004 at 3:46 pm
Michelle's solution doesn't work if field1 is defined as char(6). It will work if it is defined as varchar(5) or varchar(6) or ... - apf
February 18, 2004 at 7:37 am
APF -
I tested it on SQL2k with SP3 and it works fine. I have tested with varchar and char and it works both ways. Could you please show me why it doesn't work, so I can learn from this?
Michelle
February 18, 2004 at 7:57 am
Hello Michelle! I am also on SQL2K (SP3). My main experience is with Sybase (which is very similar to MSSQL) and I knew that your solution would not work on Sybase. I tried it on my SQLServer and it did not work there. Basically, I used your test code and changed the column definition to char(6) and it returned all 3 rows. I presumed that was because 'abcde' stored in char(6) takes all 6 bytes and therefore was being stored as 'abcde ' (so the charindex was finding a space).
February 18, 2004 at 8:06 am
I see. You changed the definition of the field. Of course it is going to give you all 3 records if you have it set as char(6) and only place 5 characters in the field. But that wasn't Robbs' Question.
If you want to get the charindex to work where you defined the field as char(6) then you would use the rtrim function:
select * from test
where charindex(' ',rtrim((field1)) > 0 and you would also get just the two rows with internal spaces.
Michelle
February 18, 2004 at 8:26 am
I just wanted to point out to Robb that your solution would only work if the char field were completely filled, which doesn't often happen in the real world. I would tend to define the field as varchar and then your inital solution and his solution (the like '% %') would both work. - apf
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply