CODEWITHSUNDEEP
sql-server
Samantha J T Star
Samantha J T Star

Reputation: 32828

In SQL Server is there a way I can define a computed column to be equal to the ASCII value of the first character of one of the columns?

I have a simple table that contains words:

CREATE TABLE [dbo].[Word] (
    [WordId]       VARCHAR (20) NOT NULL,
    [CategoryId]   INT          DEFAULT ((1)) NOT NULL,
    PRIMARY KEY CLUSTERED ([WordId] ASC),
);

Is it possible to create a new column that automatically holds the ASCII value of the first character of WordId that I could access with a SELECT? I am not very sure about SQL but I read about Computed Columns and also I wonder if I can populate the default value of a column based on the value of another column.

Upvotes: 1

Views: 33

Answers (1)

Ryan LaNeve
Ryan LaNeve

Reputation: 1244

The T-SQL ASCII function will get you the ASCII code of the first character of a character expression. Using that function, you can define your computed column (which I'm naming "Ascii") like this:

[Ascii] as (ASCII([WordId]))

The complete table definition would become:

CREATE TABLE [dbo].[Word] (
    [WordId]       VARCHAR (20) NOT NULL,
    [CategoryId]   INT          DEFAULT ((1)) NOT NULL,
    [Ascii]        as (ASCII([WordId])),
    PRIMARY KEY CLUSTERED ([WordId] ASC),
);

Example result:

INSERT INTO [Word] ([WordId], [CategoryId]) SELECT 'Testing', 1

The record created by that insert would have a value in the Ascii column of 84.

Upvotes: 3

Related Questions