custom datatype SQL server 2000

Hi All
In our DB we have info in a format of "150:1" resp "nnn:n". Its information
about Contrast Ratio.
This is of course typed as varchar. That prevents us from sorting the fields
the way we need. we are also
unable to do any kind of 'simple' compare < >
I guess in 2005 we could use custom Data Type feature. Is there any
workaround in MSSQL 2000
Thanx for any hints...
PetttA ratio (rational number) can be expressed as a decimal number. You could
add a computed column to your tabe that represents the contrast ratio as a
decimal
ALTER TABLE [YourTable]
ADD [DecimalRatio]
AS CAST(LEFT([ContrastRatio], CHARINDEX(':',[ContrastRatio]) - 1) AS numeric)
/
CAST(RIGHT([ContrastRatio], LEN([ContrastRatio]) -
CHARINDEX(':',[ContrastRatio])) AS numeric)
"Petr SIMUNEK" wrote:

> Hi All
> In our DB we have info in a format of "150:1" resp "nnn:n". Its informatio
n
> about Contrast Ratio.
> This is of course typed as varchar. That prevents us from sorting the fiel
ds
> the way we need. we are also
> unable to do any kind of 'simple' compare < >
> I guess in 2005 we could use custom Data Type feature. Is there any
> workaround in MSSQL 2000
> Thanx for any hints...
> Pettt
>
>|||Petr
Why not split the values into 2 columns? You could then build a sequence or
do a comparison using a quotient of the 2 values, right? You can always
combine them back to ratio notation for display purposes.
CREATE TABLE Foobar (
key_col VARCHAR(3) NOT NULL PRIMARY KEY
,contrast_num INT NOT NULL
,contrast_den INT NOT NULL
);
INSERT INTO Foobar VALUES ('AAA', 150, 1);
INSERT INTO Foobar VALUES ('BBB', 20, 1);
INSERT INTO Foobar VALUES ('CCC', 1, 1);
INSERT INTO Foobar VALUES ('DDD', 43, 2);
INSERT INTO Foobar VALUES ('EEE', 25, 6);
SELECT
key_col
,CAST(contrast_num AS VARCHAR(3)) + ':' + CAST(contrast_den AS CHAR(1))
AS contrast_ratio
FROM Foobar
ORDER BY contrast_num / contrast_den
;
Comparisons could work the same way.
Joe