A thick day - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A thick day

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-05-30 : 10:49:14

I'm having a really thick day - it is late on a warm friday.

I have a table with values that I need to restrict to numbers, I know it should be of type int but it's varchar and I can't change that. What's the best way to do this. I tried to use ISNUMERIC as a check constraint but kept getting errors. I used ISNUMERIC(pedno) (the column is called pedno) and it just kept complaining about the brackets!

thanks

steve

slacker
Posting Yak Master

115 Posts

Posted - 2003-05-30 : 11:03:52

What about. ISNUMERIC(LTRIM(RTRIM(pedno))).

Complaining about brackets? So its syntax error? Post sample code and error message.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 11:37:13

How about a constraint...this is for a 2 digit number


USE Northwind
GO
CREATE TABLE bk_Table (
	  col1 int IDENTITY(1,1)
	, col2 varchar(50) CHECK (col2 LIKE '[0-9][0-9]')
)
GO
INSERT INTO bk_Table(col2) SELECT '00'
GO
INSERT INTO bk_Table(col2) SELECT '99'
GO

SELECT * FROM bk_Table
GO

INSERT INTO bk_Table(col2) SELECT '999'
GO

INSERT INTO bk_Table(col2) SELECT 'AB'
GO

DROP TABLE bk_Table
GO

But what does the bol syntax of do?

LIKE '99[0-9][0-9]'

I thought that would work but it fails my inserts...

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-31 : 09:01:00

quote:

But what does the bol syntax of do?

LIKE '99[0-9][0-9]'

I thought that would work but it fails my inserts...

Not sure what that means, Brett ...

- Jeff

Edited by - jsmith8858 on 05/31/2003 09:01:16

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-31 : 12:11:39

It's an example BOL has on creating a table with a contraint.

LIKE '99[0-9][0-9]' means

any string like

'9900'
'9901'
....
'9999'

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-06-02 : 08:43:11

Thanks to all who replied.

In fact the pedno sequence is 0, 1, ... with no fixed limit so I'm not sure the [0-9] type syntax will work.

I'm trying to create this on an existing table using Enterprise Manager. Some of the existing data may be invalid so I am not checking that when creating the constraint

I put in ISNUMERIC(pedno) which it changes to (ISNUMERIC(pedno))

then when I try to save the table changes the error I get is :-

'PATIENT' table
- Unable to add constraint 'CK_NumericPedno'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ')'.

In desparation I've also tried
(ISNUMERIC(cast(pedno) AS int))

but I then get the message
'PATIENT' table
- Unable to add constraint 'CK_PATIENT'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'cast', expected 'AS'.

Which was a bit of a suprise as I have used AS.

Thanks again

steve

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-02 : 10:24:57

Valter,

Thanks...talk about a "Thick" day...

[homer]doooh[/homer]

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-02 : 10:29:11

quote:

In fact the pedno sequence is 0, 1, ... with no fixed limit

You mentioned in your original post that the column was varchar...

How does a varchar have a sequence?

Also Identity has a limit...post your DDL

Brett

8-)

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-06-03 : 02:41:22

I didn't set the system up.

The pedno is varchar, but is used to store a positive integer and so should arguably be an INT. It's actually a family number so every time we identify a new family they get a new number. At the moment there are about 40000 distinct families on the system. This number is constantly increasing (though relatively slowly). I suppose what I meant is that there is no theoretical limit to the pedno that we would go to.

What I need is to check that the number entered by the user is a number. I can't change the interface or anything like that but I can put in a check constraint or a trigger.

This came about because the number 0 is used a lot, but sometimes people put the letter O in by mistake which screws up some things that are further down the line.

Hope this clarifies it

steve

Bambola
Posting Yak Master

103 Posts

Posted - 2003-06-03 : 03:13:55

What about NOT LIKE '%[^0-9]%' ?

Bambola

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-06-04 : 04:51:09

When I tried that it complained about the syntax near the word LIKE

- I give up, I think I'll just have to beat people who mistype :-)

steve

Bambola
Posting Yak Master

103 Posts

Posted - 2003-06-04 : 05:02:16

Try this:


ALTER TABLE [dbo].[table_name] ADD 
	CONSTRAINT [CK_const_name] CHECK (not([column_name] like '%[^0-9]%'))

Bambola.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-06-05 : 08:56:13

My Saviour

Many many thanks - I hope I can return the favour some day (though not a warm friday ;-)

Thanks again

steve

LLatinsky
Starting Member

38 Posts

Posted - 2003-06-06 : 16:56:03

have you tried:
ALTER TABLE [dbo].[table_name] ADD
CONSTRAINT [CK_const_name] CHECK (isnumeric([column_name])=1)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-06 : 17:44:41

isnumeric returns a 0 or 1
so
isnumeric(pedno) = 1

(missed LLatinsky's post hiding at the bottom there :) ).

isnumeric just tests whether it can convert to one of the numeric formats (usually decimal or money is the limitting datatype)

As you are going to get an error anyway you could probably do something like
convert(int,pedno) = convert(int,pedno)

depending on what format you want.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 06/06/2003 17:45:58

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources