Re: query

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
.