Combine data types in SQL Server

Question

Is there any way to combine data types in SQL Server? i.e. have the value of a cell to be both text and number where the text is always the same?

I have a table called Contract. The ContractID field should have the value of: 'TCwxyz' where 'TC' are string characters and 'wxyz' are integers.

I have the following but it doesn't seem to be working:

CREATE TYPE TenantContracts AS CHAR(6)
CHECK (SUBSTRING(VALUE,1,2)='TC'
AND (SUBSTRING(VALUE,2,4) AS SMALLINT)

Any assistance would be appreciated.

score 0 · Answer 1 · edited May 23 '17 at 12:14

Constraints are added to the table definition, you don't need to create a type.

ALTER TABLE Contract
ADD CONSTRAINT chk_ContractID CHECK (
    SUBSTRING(ContractID, 1, 2) = 'TC'
    AND ISNUMERIC(SUBSTRING(ContractID, 3, 4)) = 1
)

This solution will accept a few incorrect values, for instance TC1.01. I'd just use this for the virtue of simplicity though, rather than trying to determine if the last 4 digits are an integer, which gets surprisingly tricky (T-sql - determine if value is integer).

Edit: If you did want to make a more robust integer check, perhaps best would be to simply check individually if each of the last 4 characters is numeric:

ALTER TABLE Contract
ADD CONSTRAINT chk_ContractID CHECK (
    SUBSTRING(ContractID, 1, 2) = 'TC'
    AND ISNUMERIC(SUBSTRING(ContractID, 3, 1)) = 1
    AND ISNUMERIC(SUBSTRING(ContractID, 4, 1)) = 1
    AND ISNUMERIC(SUBSTRING(ContractID, 5, 1)) = 1
    AND ISNUMERIC(SUBSTRING(ContractID, 6, 1)) = 1
)

Combine data types in SQL Server

1 Answers1