SQL SERVER – T-SQL Fortune Cookies

Fortune cookies contain uplifting messages that bring a smile after a good meal. Did you know they likely originated in America, not China? We’ll share more exciting facts later! While fortunes are usually pre-written, we can generate unlimited random ones using SQL Server. This post will use the complete code to walk through a T-SQL implementation for a dynamic fortune picker.

Building the Fortune Database

First, we create tables to store fortune topics, adjectives, and nouns:

-- Create tables for fortune parts
CREATE TABLE FortuneTopics (
  TopicId INT IDENTITY(1,1) PRIMARY KEY,
  Topic VARCHAR(100)
);
INSERT INTO FortuneTopics VALUES
('love'), ('money'), ('career'), ('health'), ('travel'),
('relationships'), ('success'), ('adventure'), ('happiness'), ('knowledge');
CREATE TABLE FortuneAdjectives (
  AdjectiveId INT IDENTITY(1,1) PRIMARY KEY,
  Adjective VARCHAR(100) 
);
INSERT INTO FortuneAdjectives VALUES
('great'), ('amazing'), ('challenging'), ('unexpected'), ('unfortunate'),
('exciting'), ('bright'), ('fulfilling'), ('promising'), ('rewarding');
CREATE TABLE FortuneNouns (
  NounId INT IDENTITY(1,1) PRIMARY KEY,
  Noun VARCHAR(100)
);  
INSERT INTO FortuneNouns VALUES
('surprises'), ('gifts'), ('changes'), ('luck'), ('moments'),
('opportunities'), ('adventures'), ('success'), ('happiness'), ('journey');
GO

Now, we have our complete database of fortune components.

Constructing and Selecting Random Fortunes

We can generate random fortunes with this view:

-- Create view for random fortune
CREATE VIEW RandomFortune AS
SELECT TOP 1
  CONCAT(
    UPPER(LEFT(a.Adjective, 1)), 
    SUBSTRING(a.Adjective, 2, LEN(a.Adjective)), 
    ' ',
    n.Noun,
    ' await you in your ',
    UPPER(LEFT(t.Topic, 1)),
    SUBSTRING(t.Topic, 2, LEN(t.Topic)),
    '.'
  ) AS Fortune
FROM FortuneAdjectives a
CROSS JOIN FortuneNouns n
CROSS JOIN FortuneTopics t
ORDER BY NEWID();
GO

To get a random fortune, we query the view:

-- Generate random fortunes
SELECT Fortune
FROM RandomFortune;

This dynamically returns creative fortunes like:

• Great surprises await you in your love.
• Amazing gifts await you in your relationships.
• Promising opportunities await you in your career.
• Exciting journey awaits you in your adventure.
• Bright changes await you in your success.
• Rewarding luck awaits you in your money.
• Fulfilling happiness awaits you in your health.
• Fortunate moments await you in your knowledge.
• Challenging adventures await you in your travel.
• Unexpected success awaits you in your happiness.

Fun Fortune Cookie Facts

• Fortune cookies likely originated from Japanese immigrants in California, not China.
• Over 3 billion fortune cookies are produced annually in the United States.
• The largest fortune cookie measured over 8 feet wide!
• The Chinese characters on fortunes are often meaningless and added for exotic appeal.
• Fortune cookies are served as desserts, not with meals, in China.

You can follow me on X (twitter).

Reference: Pinal Dave (https://blog.sqlauthority.com)