May 5, 2015 at 9:55 am
I have a table where i have to make a check constraint that states the first 3 characters of the customerid field must be the first 3 characters of the company name I am so lost I looked everywhere. can someone give me an idea or tell me where to look for this answer thankyou
May 5, 2015 at 11:24 am
Can you provide the DDL for the table?
Is the Company Name in the same table?
You could do something like this:
IF OBJECT_ID('tempdb..#company', 'U') IS NOT NULL
BEGIN;
DROP TABLE #company;
END;
CREATE TABLE #company
(
CompanyId VARCHAR(10),
CompanyName VARCHAR(50),
CONSTRAINT CK_Company_CompanyId CHECK (LEFT(CompanyId, 3) = LEFT(CompanyName, 3))
);
/* Succeeds */
INSERT INTO #company
(CompanyId, CompanyName)
VALUES
('JAC001', -- CompanyId - varchar(10)
'JACK CORBETT' -- CompanyName - varchar(50)
);
/* Fails */
INSERT INTO #company
(CompanyId, CompanyName)
VALUES
('JA0001', -- CompanyId - varchar(10)
'JACK CORBETT' -- CompanyName - varchar(50)
);
SELECT
*
FROM
#company AS C;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 11, 2015 at 9:25 am
you can join the table to itself if the values are in the same table ( Or just join both tables if it's not the case) and use SUBSTRING; I did someting similar recently and got help on stackoverflow ( Ignore my argument with 1 of the commenters, he was a a-hole)
select distinct c1.cust_id, substring(c2.cust_name,1,3) as First3Characters, c2.cust_name
from company c1
join company c2 on substring(c1.cust_id,1,3)=substring(c2.cust_name,1,3)
group by c1.cust_id
Essentially you're asking "Give me all the company IDs where the first 3 characters match the first 3 characters of the Company Name).
In the SUBSTRING function, you specify the column you're using, the first digit is the character you start with, and the second 1 is how many characters you're taking from that value.
Ex: SUBSTRING("sqlservercentral",9,7) will return just "central".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply