0

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

rpax
  • 4,468
  • 7
  • 33
  • 57
cindy
  • 35
  • 1
  • 3
  • 7

1 Answers1

0

If you are looking to validate the Address to always start with NO, followed by a numeric value, then 

ALTER TABLE Customers WITH NOCHECK
ADD CONSTRAINT cs_address
CHECK
(
        CHARINDEX('no', Address) >= 1 
    AND ISNUMERIC(LTRIM(RTRIM
                (SUBSTRING (Address, CHARINDEX('no', Address) + 2, 7)))) = 1
)
Vikdor
  • 23,934
  • 10
  • 61
  • 84
  • Oh! So, if you want to allow addresses that contain "NO" but need not start with it, then please see the updated answer, @cindy. – Vikdor Dec 17 '13 at 03:55
  • I just try that query but i got the problem. If i insert with value 'Jalan Jakarta NO 10' than show messages : The INSERT statement conflicted with the CHECK constraint. I think it because before the word 'NO'. But i want is, when i insert with value 'Jalan Jakarta NO 10' it should be executed – cindy Dec 17 '13 at 03:59
  • sorry if I misunderstood, but where i can see the updated answer? @vikdor – cindy Dec 17 '13 at 04:25
  • Oh, OK. But i get message : Cannot find the object "Address" because it does not exist or you do not have permissions. what should i do? – cindy Dec 17 '13 at 05:00
  • Address is supposed to be your column name! @cindy – Vikdor Dec 17 '13 at 05:16
  • my mistake :p. that query was successful .Thank you very much Vikdor :) – cindy Dec 17 '13 at 06:06