Re: query

the isnumeric function is probably the most efficient way of accomplishing
this task

--
Michael Abair
Programmer / Analyst
Chicos FAS Inc.


"Arnie Rowland" <arnie@xxxxxxxx> wrote in message
news:%23ZcV%23YZ%23GHA.4704@xxxxxxxxxxxxxxxxxxxxxxx
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




.

Relevant Pages

  • Re: query
    ... WHEN ColumnNameHere LIKE '%%' THEN '00' ... Easier to understand, but incorrect as well. ... characters have to be numeric", but they do allow a test for "no ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.mseq)
  • Re: Inserting line breaks ( ) in SQL DB
    ... characters aren't needed when copy data between program variables. ... >> string with '@' so that backslashes within the string are not interpreted ... >> as escape characters. ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: DTS text file import
    ... formatting issue is the cause, ... Most good judgment comes from experience. ... characters long has a carriage return after it. ... right clicking on Tables and Importing Data and it did the same thing. ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS text file import
    ... Arnie Rowland, Ph.D. ... Most good judgment comes from experience. ... is only 7 characters long has a carriage return after it. ... right clicking on Tables and Importing Data and it did the same thing. ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS text file import
    ... between 7 and 8 characters long. ... Most good judgment comes from experience. ... right clicking on Tables and Importing Data and it did the same thing. ... experienced a 'length limitation'. ...
    (microsoft.public.sqlserver.dts)