By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Finding dashes/hyphens in a column.

P: n/a
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

Richard

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On 19 Jan 2005 07:46:49 -0800, ri************@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)
Jul 23 '05 #2

P: n/a
(ri************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

P: n/a
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!

Jul 23 '05 #4

P: n/a
Sorry, that should read
"The first 4 characters are hyphens rather than spaces" (not dashes!)

Jul 23 '05 #5

P: n/a
On 20 Jan 2005 02:21:21 -0800, ri************@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.)
WHERE text_body like '____-------%'
AND text_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)
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.