Finding dashes/hyphens in a column.

Hi,

I'm having trouble running the following query:

select * from message where text_body like ' ----%'

ie, five spaces followed by at least ten hyphens. The query doesn't
complete, so eventually I cancel it. If I remove the hyphens from the
query ("... like ' %'") then it runs fine (though it doesn't find
the correct data).

Am I confusing SQL Server by using a wildcard or regular expression?
(I'm using SQL Server 2000 - 8.99.760).
Thanks in advance for any help

RichardOn 19 Jan 2005 07:46:49 -0800, richard_thomas@.bigfoot.com wrote:

>I'm having trouble running the following query:
>select * from message where text_body like ' ----%'
>ie, five spaces followed by at least ten hyphens. The query doesn't
>complete, so eventually I cancel it. If I remove the hyphens from the
>query ("... like ' %'") then it runs fine (though it doesn't find
>the correct data).
>Am I confusing SQL Server by using a wildcard or regular expression?
>(I'm using SQL Server 2000 - 8.99.760).
>Thanks in advance for any help

Hi Richard,

I couldn't reproduce this behaviour. The following script returns exactly
the one row I expect to be returned:

create table message (text_body varchar(50))
go
insert message values (' ---- should be returned')
insert message values (' --- one dash short')
insert message values (' ---- one space short')
insert message values (' ---- one space too much')
go
select * from message where text_body like ' ----%'
go
drop table message
go

Can you post a script that will reproduce the buggy behaviour on my
machine?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(richard_thomas@.bigfoot.com) writes:
> I'm having trouble running the following query:
> select * from message where text_body like ' ----%'
> ie, five spaces followed by at least ten hyphens. The query doesn't
> complete, so eventually I cancel it. If I remove the hyphens from the
> query ("... like ' %'") then it runs fine (though it doesn't find
> the correct data).

How many rows are there in message? What query plans do you get in
the two cases?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hmm.

I'm beginning to have a feeling that the 'spaces' aren't in fact
spaces.

By doing this, it seems to work:

select * from message where text_body like '____---%'

but also finds strings such as

'----'
ie the first 4 characters are hyphens rather than dashes.

Is there a wildcard that matches whitespace?
Thanks again for your help!|||Sorry, that should read
"The first 4 characters are hyphens rather than spaces" (not dashes!)|||On 20 Jan 2005 02:21:21 -0800, richard_thomas@.bigfoot.com wrote:

>Is there a wildcard that matches whitespace?

Hi Richard,

Unfortunately, no.

You should locate rows that you suspect to have non-space whitespace,
using (e.g.)
WHEREtext_body like '____---%'
ANDtext_body NOT like ' ---%'

Then, inspect the real contents of text_body in such rows:
SELECT ASCII (SUBSTRING (text_body, 1, 1)),
ASCII (SUBSTRING (text_body, 2, 1)),
ASCII (SUBSTRING (text_body, 3, 1)),
ASCII (SUBSTRING (text_body, 4, 1))
FROM ...
WHERE ...

Once you know that, you can start thinking how to match all variations you
may have in your data.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)