Is there a check for numerics?

In my conversion from another system to ours, they have an alphanumeric
field that puts an "A" in front of their check numbers in some cases, has no
Alphas in some and all Alphas in others.

I could always do a case statement looking for the A and then doing a
substring to strip it.  But it is a little more difficult for the others.
There aren't many (out of about 5000), but enought to be irritating.

Thanks,

Tom

UPDATE Checkbook
  SET check_nbr = REPLACE (check_nbr, 'A', '');

Then add a CHECK() constraint to prevent them in the future.  I have an
article at www.dbazine.com on "mopping the flor and then fixing the
leak" that deals with this.

I agree, but I can't do that here.

This is a one time conversion.  The database I am getting the data from
allows alphas, ours does not.

Your example works fine for 'A' example, but there are also some others that
just have alphas in them that I need to convert, such as 'MANENTRY' or
'FLEX1234'.

I just need my Sql Script look at the field and if non-numeric, make it
numerice (for this time only).

Thanks,

Tom

On Sun, 18 Sep 2005 19:47:51 -0700, tshad wrote:

Show quoteHide quoteHi Tom,

You didn't specify what you want to return in case there are no numerics
at all in the data (such as the 'MANENTRY' example you gave above).

You also didn't specify if the data is always zero or more non-numeric
followed by zero or more numeric.

Try if this helps. If it doesn't, then please provide better specs.

DECLARE @a varchar(20)
SET @a = 'FLEX1234'
SELECT RIGHT('X' + @a, PATINDEX('%[^0-9]%', REVERSE('X' + @a)) - 1)

Best, Hugo

Show quote Hide quote I hadn't really thought it all the way through, yet.  I was only curious if
there was a "IsNumber" type of function that would work in a case statement.

Actually, would be good would be to be able to find a non-numeric, change it
to some variable I have set to some number (say 100) and have it increment
by one each time it assigned it.

So if it finds a non-numeric, it would set

Something like:

DECLARE @seqNumber int
SELECT @seqNumber = 100
SELECT CASE  When CheckNumber = non-numeric THEN CheckNumber =
@seqNumber,@seqNumber = @seqNumber + 1 from Table...

May be a little procedural for Sql.

Thanks,

Tom
Show quoteHide quote

On Mon, 19 Sep 2005 17:06:03 -0700, tshad wrote:

(snip)
Hi Tom,

Maybe. Maybe not. I must say (to me, at least), your description is a
bit unclear. Maybe because you haven't thought it all the way through?

I can do quite a lot in SQL, but one thing I can't do - make it adhere
to specs that are not yet written.

I suggest that you first think it all the way through. Don't code - just
think. Collect examples of how the data really looks like. Collect more
examples of how the data might start to look like in the future. Then
work out how each of the examples has to be transformed.

Only start coding when this all is done. If the coding is troublesome,
feel free to ask for help again - but this time include the complete
specs. Also include
* Table structure in the form of CREATE TABLE statements
* Sample data in the form of INSERT statements
* Requested output
See www.aspfaq.com/5006 for more details.

Best, Hugo

Show quote Hide quote I usually do this.

But in this case, I am not sure what that would gain.

What I have is a one time shot, but I may use this for something else.

I have some data coming from another database that uses AlphaNumeric for
their check numbers.  Most of their checks are appended with an "A" for
direct deposit (ACH).  I have no problem with this.  I just test for it and
strip it if it is there.

Where I have a problem is that they also use the field for a Manual
adjustment and they put things like "MANADJ" or "ADJUSTMENT" or "FLEXADJ" or
"CHKMOD" or something like that.  It could also be "AC153DDD" - whatever
strikes their fancy.

Our numbers start from about 10000, so if I take all these non-numeric
numbers and change them to some number (100 for example) - we can find and
deal with them after they get into our system.  Since our system only takes
Integers, we would get an error with these codes.

This was just an example of what I was looking for:

DECLARE @seqNumber int
SELECT @seqNumber = 100
SELECT CASE  When CheckNumber = non-numeric THEN CheckNumber =
@seqNumber,@seqNumber = @seqNumber + 1 from Table...

Starting at the seqNumber = 100
if the Checknumber is not a number, change it to whatever the seqNumber is
and change the seqNumber to whatever it is + 1

Then I can take that data and write it to a CSV file that we will transfer
to our system.

I just didn't know how to test for a non-numeric to accomplish this.

Tom

On Tue, 20 Sep 2005 16:15:41 -0700, tshad wrote:

Hi Tom,

Well, for a one time shot, I'd take the easiest way and use two
consecutive queries.

First use the formula I gave earlier in this thread to ge the numerics
on the right end of the data. This will result in an empty string if the
data doesn't end in numerics. Cast the result to int (result will be 0
for empty string).

Now, use the following to update the 0's to an increasing number (that
is what I think you want - but since you still didn't post sample data
and expected output, it's still just a guess).

UPDATE YourTable
SET    YourColumn = (SELECT COUNT(*)
                     FROM   YourTable AS a
                     WHERE  a.PKColumn <= YourTable.PKColumn
                     AND    a.YourColumn = 0)
WHERE  YourColumn = 0

(untested)

Best, Hugo

See:
http://www.aspfaq.com/show.asp?id=2390

or use one of my own functions:

CREATE FUNCTION dbo.IsSomethingInteger(@num VARCHAR(64))
RETURNS BIT
BEGIN
RETURN CASE WHEN
  (LEFT(@num,1) LIKE '[0-9]' OR LEFT(@num,1)='-' AND LEN(@num)>1)
  AND PATINDEX('%[^0-9]%', SUBSTRING(@num, 2, 64)) = 0
THEN 1 ELSE 0 END
END

CREATE FUNCTION dbo.IsSomethingNumeric(@num VARCHAR(64))
RETURNS BIT
BEGIN
RETURN CASE WHEN
  LEN(@num)>0
  AND @num NOT LIKE '%[^0-9.-]%'
  AND (
   @num NOT LIKE '%.%'
   OR LEN(@num)-LEN(REPLACE(@num,'.',''))=1
    AND @num LIKE '%[0-9]%'
  ) AND (
   @num NOT LIKE '%-%'
   OR LEN(@num)-LEN(REPLACE(@num,'-',''))=1
    AND LEFT(@num,1)='-'
    AND LEN(@num)>1
  )
THEN 1 ELSE 0 END
END

Razvan

Update Lock
select variable in from statement
Date/String format
PRIMARY KEY
Another case for IDENTITY