SQL Server - Add Constraint using CHARINDEX and Isnumeric

Question

I got a little trouble with SQL. This is the table Customers :

ID      Name     Address                Phone

KP001   Bill     Jl Bali NO 27          81976524451
KP002   Jane     Jl Sandang NO 5        81876537521
KP003   Dion     Jl Kebon Jeruk NO 7    81684234913

the original structure table Customer :

CREATE TABLE Customer
(
ID char(5)primary key not null,
Name varchar(20),
Address varchar(30),
Phone varchar(12),
constraint cs2 check (LEN(ID)=5),
constraint cs3 check (ID like 'KP[0-9][0-9][0-9]'),
)

and I want to change table structure using Alter Table. I want to Add constraint to table Customers which Address after the 'no' must be a number.

First, in Indonesia there are street names using the 'NO' as an indicator street number. And requires the Customer table after the 'NO' is a number. I've tried with this query :

ALTER TABLE Customers WITH NOCHECK
ADD CONSTRAINT cs_address 
CHECK (CHARINDEX('no',Address)>1 AND
ISNUMERIC ( SUBSTRING (Address,7,2)) =1)

I know the query incorrect, but can you help me to fix the error and get the right results?

*sorry if my english is not good

That tends to indicate that you have structure within your `Address` column, which tends to be a bad thing. Is there a reason you're not storing `Address_Without_No` and `No` as *two* separate columns (which you then just bring together for display)? Also `ISNUMERIC` doesn't do what you think it does - unless you consider `ABC NO £` to be a valid entry (and ignoring for now that I also assume that the letters `no` might legitimately be found elsewhere in the spelling of an address) — Damien_The_Unbeliever, Dec 16 '13 at 07:49
First, in Indonesia there are street names using the 'NO' as an indicator street number. And requires the Customer table after the 'NO' is a number — cindy, Dec 16 '13 at 07:59

score 1 · Answer 1 · answered Dec 16 '13 at 08:04

1

As I said in my comment, I'd restructure the table to be this instead:

CREATE TABLE Customers (
    ID int not null,
    Name varchar(10) not null,
    Address varchar(50) not null,
    Address_No int not null, --Could also be varchar if we need to support leading 0s
    Phone varchar(10) not null,
    Complete_Address as Address + ' NO ' + CONVERT(varchar(10),Address_No),
    constraint CK_Address_No CHECK (Address_No > 0)
)

Which enforces the constraint a lot more strongly.

answered Dec 16 '13 at 08:04

Damien_The_Unbeliever

234,701
27
340
448

the original structure table Customer : CREATE TABLE Customer ( ID char(5)primary key not null, Name varchar(20), Address varchar(30), Phone varchar(12), constraint cs2 check (LEN(ID)=5), constraint cs3 check (ID like 'KP[0-9][0-9][0-9]'), ) and I want to change table structure using Alter Table, but i don't know how it works – cindy Dec 16 '13 at 08:32

SQL Server - Add Constraint using CHARINDEX and Isnumeric

1 Answers1