Re: query

I see your point Hugo.

Your illustration of using the double negative will cover all
possibilities -pure or mixed alphanumerics, whereas my method only covers
all alpha or all numeric -but not the mixed possibilities.

For the OP's situation as presented (all alpha or all numeric), either will
work, but your method is definitely more robust.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf


"Hugo Kornelis" <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:beb2k21s9ptltdqsou2dojs4ejbmpdcbt4@xxxxxxxxxx
On Thu, 26 Oct 2006 13:41:15 -0700, Arnie Rowland wrote:

Hugo,

Instead of combining NOT LIKE and [^0-9], wouldn't that be easier to
understand as:

SELECT CASE
WHEN ColumnNameHere LIKE '%[0-9]%' THEN '00'
ELSE ''
END + ColumnNameHere
FROM TableNameHere;

(I've always hated double negatives...)

Hi Arnie,

Easier to understand, but incorrect as well. Try it with '1s3' or 'af7'
as input data.

Double negatives are a bane for language, but a boon for SQL. Not only
for relational division, but also for constructing LIKE clauses. The
very limited regexp powers of LIKE don't allow for a test of "all
characters have to be numeric", but they do allow a test for "no
character may not be numeric".

Of course, if the length of the string is guaranteed to be always three
characters, you could also use LIKE '[0-9][0-9][0-9]'.

--
Hugo Kornelis, SQL Server MVP


.