How do I Read User Input in a Compact Java 25 Program?

By Wayan in Basic, Java 25 0 Comment

Java 25 (via JEP 512: Compact Source Files and Instance Main Methods, finalized in Java 25) makes reading user input dramatically simpler. You no longer need a class declaration, a public static void main(String[] args) signature, or even System.out.println / Scanner boilerplate.

The New IO Class

Java 25 introduces the java.lang.IO class, which is automatically imported in compact source files. It provides three convenient static methods:

Method Purpose
IO.print(x) Print without newline
IO.println(x) Print with newline
IO.readln(prompt) Print a prompt and read a line from stdin

Compact Example

Here’s a compact Java 25 program that reads user input:

void main() {
    String username = IO.readln("Username: ");
    String password = IO.readln("Password: ");
    int result = Integer.parseInt(IO.readln("What is 2 + 2: "));

    if (username.equals("admin") && password.equals("secret") && result == 4) {
        IO.println("Welcome to Java Application");
    } else {
        IO.println("Invalid username or password, access denied!");
    }
}

Key Points

  • No class declaration required — the file becomes an implicitly declared class.
  • No String[] args — you can just write void main().
  • No import statements — java.base and java.lang.IO are auto-imported.
  • No Scanner — IO.readln(...) handles the prompt and line read in one call.
  • The method can be void main() or void main(String[] args); static is optional.

Running It

Save the code as Login.java and run it directly (no compile step needed):

java Login.java

When You Still Need Scanner

IO.readln only returns String. If you need typed input like int, double, etc., you either:

  1. Parse it yourself (as shown above with Integer.parseInt), or
  2. Fall back to java.util.Scanner for its nextInt(), nextDouble(), etc.

For most simple interactive programs, IO.readln + parsing is the cleanest approach in Java 25.

How do I Use an Instance main Method in Java 25?

By Wayan in Basic, Java 25 0 Comment

Java 25 finalizes JEP 512: Compact Source Files and Instance Main Methods, which was previewed in earlier JDK releases. This feature makes Java far more approachable for beginners and reduces boilerplate for small programs and scripts.

What Changed?

Traditionally, every Java program required this ceremony:

public class HelloWorld {
    public static void main(String[] args) {
        System.out.println("Hello, World!");
    }
}

With Java 25, the main method no longer needs to be:

  • public
  • static
  • Declared with a String[] args parameter

The New Rules

The JVM launcher now looks for a main method in this order of preference:

  1. static void main(String[] args) — traditional form
  2. static void main() — no parameters
  3. void main(String[] args) — instance method with args
  4. void main() — instance method, no args simplest form

If an instance main method is found, the JVM will implicitly create an instance of the enclosing class using its no-argument constructor, then invoke main on it.

Example 1: The Simplest Instance main

public class Hello {
    void main() {
        System.out.println("Hello from an instance main!");
    }
}

That’s the entire program. No static, no String[] args, no public.

Run it with:

java Hello.java

Example 2: Instance main With Fields and Helper Methods

Because main is now an instance method, it can freely use instance fields and call other instance methods without needing static everywhere:

public class Greeter {
    private final String greeting = "Hello";

    void main() {
        greet("Java 25");
        greet("Developers");
    }

    void greet(String name) {
        System.out.println(greeting + ", " + name + "!");
    }
}

Notice that greet is also a plain instance method — no static modifier needed.

Example 3: Combined With Compact Source Files

Java 25 also allows you to omit the enclosing class entirely (Compact Source File):

void main() {
    System.out.println("No class declaration required!");
}

Save this as Demo.java and run:

java Demo.java

The compiler implicitly wraps the code in a synthetic class for you.

Example 4: Instance main With Arguments

If you still need command-line arguments, just declare them:

public class Echo {
    void main(String[] args) {
        for (String arg : args) {
            System.out.println("Argument: " + arg);
        }
    }
}

Requirements & Caveats

  • The enclosing class must have an accessible no-argument constructor (the default one is fine if you don’t declare any constructor).
  • The class cannot be abstract.
  • If both a static and an instance main exist, the static one wins (per the resolution order above).
  • The instance main method cannot be private. It must be at least package-private.
  • To run a single source file directly (java Foo.java), you don’t need to compile first — the launcher handles it.

Why This Matters

  • Lower barrier for beginners — no need to explain public, static, String[] args, or classes on day one.
  • Cleaner scripts — small utilities and experiments become much more concise.
  • Smoother learning curve — students can gradually introduce classes, static, and access modifiers as they progress, rather than all at once.

Quick Comparison

Style Java ≤ 20 Java 25
public static void main(String[] args) ✅ Required ✅ Still works
static void main() ❌ ✅
void main(String[] args) ❌ ✅
void main() ❌ ✅
No class declaration ❌ ✅ (Compact Source File)

Instance main methods, combined with compact source files, make Java 25 one of the most beginner-friendly releases in the language’s history — while remaining fully backward compatible with every existing Java program.

How do I Run My First Java 25 Program Without Creating a Class?

By Wayan in Basic, Java 25 0 Comment

Java 25 makes it official: you can now write and run a program without declaring a class, without public static void main(String[] args), and even without import statements for common APIs. This feature is called Compact Source Files and Instance Main Methods (JEP 512, finalized in Java 25).

Let’s walk through it step by step.

1. Prerequisites

  • JDK 25 installed and available on your PATH
  • Verify with:
java --version
javac --version

Both should report version 25.

2. Write the Program

Create a plain text file named Hello.java. That’s it — no class, no public static, no ceremony:

void main() {
    IO.println("Hello, Java 25!");
}

A few things worth noticing:

  • There is no class declaration. The compiler wraps the code in an implicitly declared class for you.
  • main is an instance method (not static) and takes no arguments (the String[] args parameter is optional now).
  • IO.println(...) comes from the new java.io.IO class, which is auto-imported in compact source files — no System.out.println and no import needed.

3. Run It Directly with java

Since JDK 11, you can run a single-file source program directly. In Java 25, this works beautifully with the new compact form:

java Hello.java

Expected output:

Hello, Java 25!

No javac step is required. The launcher compiles the file in memory and runs it.

4. A Slightly Richer Example

You can still read input, do logic, and use any Java API — just without the boilerplate:

void main() {
    var name = IO.readln("What is your name? ");
    IO.println("Welcome, " + name + "!");

    for (int i = 1; i <= 3; i++) {
        IO.println("Count: " + i);
    }
}

Run it the same way:

java Hello.java

5. When You Outgrow It

Compact source files are meant for learning, scripting, and quick experiments. When your program grows, you can gradually add:

  1. A String[] args parameter to main when you need CLI arguments.
  2. Additional methods and fields directly in the file (they become members of the implicit class).
  3. Finally, an explicit class declaration — at which point you have a regular Java source file.

The transition is smooth because the language rules are a strict superset of traditional Java.

6. Common Pitfalls

Issue Cause Fix
error: class ... is public, should be declared in a file named ... You added public to a helper class in the same file Remove public — the implicit class is unnamed
IO cannot be resolved You’re not on JDK 25 (or using an older preview flag) Upgrade to JDK 25; no --enable-preview needed anymore
main not found Wrong signature (e.g., returns int) Use void main() or void main(String[] args)

Summary

To run your first Java 25 program without creating a class:

  1. Install JDK 25.
  2. Create Hello.java containing just a void main() method.
  3. Use IO.println(...) — no imports needed.
  4. Run it with java Hello.java.

This is the shortest path from “I have JDK installed” to “my program is running” that Java has ever offered — perfect for beginners and for quick prototypes alike.

Taming a Dragon With Your Mouse: Building Dragon Cursor Chase in a Single Java File

By Wayan in 2D API, Animation, Swing, Swing Loops 0 Comment

There’s something delightful about tiny, self‑contained graphics demos. No frameworks, no build systems, no node_modules folder the size of a small moon — just one .java file, javac, and a window that does something you didn’t expect from Swing.

Dragon Cursor Chase

Dragon Cursor Chase

DragonCursorChaseMinimal is exactly that: a neon dragon made of 22 glowing circles that slithers after your cursor, blinks lazily at you, and breathes fire when you click. It fits in about 150 lines. Let’s take a tour of how it works and why the tricks inside it are worth stealing for your own doodles.

The idea in one paragraph

The dragon is a chain of points. The head chases the mouse with a simple spring‑like ease. Every other body segment just follows the one in front of it at a fixed distance. Render each point as a glowing circle, rotate a stylized head on top of the first point, and sprinkle particles when the user clicks. That’s the whole trick.

The body: a follow‑the‑leader chain

The state of the dragon is two parallel arrays of coordinates:

static final int N = 22;
final double[] x = new double[N], y = new double[N];

The head (index 0) does the actual chasing — a classic exponential smoothing toward the target:

x[0] += (mouseX - x[0]) * .28;
y[0] += (mouseY - y[0]) * .28;

That .28 is the “springiness”. Lower values make a lazier, more elegant dragon; higher values make a caffeinated one.

The rest of the body is where the magic happens. Each segment is pulled toward the previous one, but clamped to a fixed distance of 19 pixels:

for (int i = 1; i < N; i++) {
    double dx = x[i] - x[i - 1], dy = y[i] - y[i - 1];
    double d = Math.max(.001, Math.hypot(dx, dy));
    x[i] = x[i - 1] + dx / d * 19;
    y[i] = y[i - 1] + dy / d * 19;
}

This is sometimes called a distance constraint or a one‑pass rope solver. It gives you smooth, snake‑like body motion for basically free. The Math.max(.001, …) guard is a tiny but important detail — it prevents a division by zero when two segments occupy the same point (which happens on the very first frame).

Getting the head to point the right way

Because we saved the head’s previous position, we can compute its heading with a single atan2:

angle = Math.atan2(y[0] - oldY, x[0] - oldX);

That angle is then used both to rotate the head graphics and to aim the fire breath. It’s a nice example of how one derived value can unify several visual effects.

Painting the body: glow for the price of one extra oval

Each segment is drawn twice — once large and translucent for the halo, once smaller and opaque for the core:

g.setColor(new Color(c.getRed(), c.getGreen(), c.getBlue(), 55));
g.fillOval((int) x[i] - r - 5, (int) y[i] - r - 5, (r + 5) * 2, (r + 5) * 2);
g.setColor(c);
g.fillOval((int) x[i] - r, (int) y[i] - r, r * 2, r * 2);

That’s a poor‑man’s bloom effect. No shaders, no compositing tricks, just two ovals per segment. The radius shrinks along the body (5 + 15 * t) so the tail tapers naturally, and the hue drifts slightly with Color.getHSBColor(.44 + .10 * t, …) for a subtle gradient from teal to sea‑green.

The loop iterates backwards (for (int i = N - 1; i > 0; i--)), so bigger segments closer to the head end up painted on top of smaller tail segments. Painter’s algorithm at its most literal.

The head: a rotated coordinate system

Rather than doing trigonometry to place each eye, ear, and nostril, the code creates a child Graphics2D, translates it to the head position, and rotates it:

Graphics2D h = (Graphics2D) g.create();
h.translate(x[0], y[0]);
h.rotate(angle);

Now every subsequent drawing call — the snout Path2D, the triangular ears, the eyes — can be written in the dragon’s own local space, with x pointing forward. Notice how the two ears are just mirrored polygons:

h.fillPolygon(new int[]{-12, -22, 1}, new int[]{-17, -39, -20}, 3);
h.fillPolygon(new int[]{-12, -22, 1}, new int[]{ 17,  39,  20}, 3);

The two eyes get a cheap blinking animation by squishing their vertical radius with a sine wave:

double eyeH = 6 * Math.max(.12, Math.abs(Math.sin(time * .22)));

The Math.max(.12, …) keeps the eyes from ever fully closing, so the dragon looks alert rather than sleepy. And crucially, h.dispose() is called when we’re done — always dispose the graphics contexts you create(), or you’ll leak state into the parent.

Fire breath: particles with a lifetime

Clicking sets a timestamp:

public void mousePressed(MouseEvent e) {
    fireUntil = System.currentTimeMillis() + 550;
}

For 550 milliseconds after the click, each frame spawns four Flame particles, each with:

  • a position offset forward from the head by 38 pixels along angle,
  • a velocity fanned out by up to ±0.275 radians from the heading,
  • a random lifetime between 28 and 46 frames.

The particle physics is trivial but tuned:

void update() {
    x += vx;
    y += vy;
    vx *= .965;
    vy = vy * .965 + .025;
    life--;
}

Horizontal velocity decays; vertical velocity decays and gets a gentle downward tug. Result: flames shoot forward, slow down, and drift down like hot embers.

Rendering each flame is a single circle whose size, color, and alpha are all functions of remaining life:

float age = f.life / (float) f.maxLife;
int r = Math.max(2, (int) (18 * age));
Color c = Color.getHSBColor(.02f + .12f * age, 1, 1);

Young flames are bigger, more yellow, and more opaque. Old flames shrink into small, dim red pixels before vanishing. That single hue interpolation from .02 (red) to .14 (orange‑yellow) is doing a lot of aesthetic heavy lifting.

The background: one gradient to rule them all

The dark deep‑blue backdrop is drawn once per frame as a radial gradient:

g.setPaint(new RadialGradientPaint(
        new Point2D.Double(getWidth() / 2.0, getHeight() / 2.0),
        Math.max(getWidth(), getHeight()) * .7f,
        new float[]{0, 1},
        new Color[]{new Color(22, 35, 76), new Color(3, 5, 14)}));
g.fillRect(0, 0, getWidth(), getHeight());

It’s the cheapest way to make the scene feel like it has depth. The neon colors of the dragon pop against it precisely because the corners fade almost to black.

The animation loop: javax.swing.Timer at 60 fps

There’s no thread management, no game loop, no Thread.sleep in a run() method. Just:

new Timer(16, e -> update()).start();

A javax.swing.Timer fires its callback on the Event Dispatch Thread every 16 ms — roughly 60 fps. Inside update() we move the dragon, tick the particles, then call repaint(). Because everything runs on the EDT, there are no synchronization concerns between input (mouse events) and rendering. For a demo of this size, it’s the right tool.

Why this pattern is worth stealing

A few takeaways that generalize beyond dragons:

  1. Chains of points + a distance constraint are a shockingly good approximation of ropes, snakes, tentacles, and hair. One loop, no physics library.
  2. Translate + rotate a child Graphics2D whenever you’re drawing a directional object. Trying to bake rotation into every coordinate by hand is a recipe for off‑by‑one‑radian bugs.
  3. Two‑pass “halo + core” drawing gives you a convincing glow without touching any compositing APIs or BufferedImages.
  4. Particles = position + velocity + life. That’s genuinely all you need for 90% of “juice” effects.
  5. javax.swing.Timer is fine. For interactive art at 60 fps, the EDT will not let you down.

Running it

Save the file as DragonCursorChaseMinimal.java and, from the same folder:

javac DragonCursorChaseMinimal.java
java DragonCursorChaseMinimal

A 720×1280 window opens. Move your mouse. The dragon follows. Click and hold — it breathes fire in whichever direction it’s currently pointed. Let go and the flames drift, cool, and disappear.

That’s it. One file, one dragon, zero dependencies. Sometimes the best way to remember why you liked programming is to make something that has no business existing and put it on your screen for an afternoon.

The Complete Code

import javax.swing.*;
import java.awt.*;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.geom.Ellipse2D;
import java.awt.geom.Path2D;
import java.awt.geom.Point2D;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Random;

public class DragonCursorChaseMinimal extends JPanel {
    static final int N = 22;
    final double[] x = new double[N], y = new double[N];
    final List<Flame> fire = new ArrayList<>();
    final Random random = new Random();
    double mouseX = 360, mouseY = 640, angle, time;
    long fireUntil;

    DragonCursorChaseMinimal() {
        setPreferredSize(new Dimension(720, 1280));
        setBackground(new Color(4, 7, 20));
        for (int i = 0; i < N; i++) {
            x[i] = mouseX;
            y[i] = mouseY + i * 19;
        }

        MouseAdapter mouse = new MouseAdapter() {
            public void mouseMoved(MouseEvent e) {
                aim(e);
            }

            public void mouseDragged(MouseEvent e) {
                aim(e);
            }

            public void mousePressed(MouseEvent e) {
                fireUntil = System.currentTimeMillis() + 550;
            }

            void aim(MouseEvent e) {
                mouseX = e.getX();
                mouseY = e.getY();
            }
        };
        addMouseMotionListener(mouse);
        addMouseListener(mouse);
        new Timer(16, e -> update()).start();
    }

    public static void main(String[] args) {
        SwingUtilities.invokeLater(() -> {
            JFrame frame = new JFrame("Dragon Cursor Chase");
            frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
            frame.setContentPane(new DragonCursorChaseMinimal());
            frame.pack();
            frame.setLocationRelativeTo(null);
            frame.setVisible(true);
        });
    }

    void update() {
        double oldX = x[0], oldY = y[0];
        x[0] += (mouseX - x[0]) * .28;
        y[0] += (mouseY - y[0]) * .28;
        angle = Math.atan2(y[0] - oldY, x[0] - oldX);

        for (int i = 1; i < N; i++) {
            double dx = x[i] - x[i - 1], dy = y[i] - y[i - 1];
            double d = Math.max(.001, Math.hypot(dx, dy));
            x[i] = x[i - 1] + dx / d * 19;
            y[i] = y[i - 1] + dy / d * 19;
        }

        if (System.currentTimeMillis() < fireUntil) emitFire();
        for (Iterator<Flame> it = fire.iterator(); it.hasNext(); ) {
            Flame f = it.next();
            f.update();
            if (f.life <= 0) it.remove();
        }
        time += .05;
        repaint();
    }

    void emitFire() {
        for (int i = 0; i < 4; i++) {
            double a = angle + (random.nextDouble() - .5) * .55;
            double speed = 7 + random.nextDouble() * 6;
            fire.add(new Flame(x[0] + Math.cos(angle) * 38,
                    y[0] + Math.sin(angle) * 38,
                    Math.cos(a) * speed, Math.sin(a) * speed,
                    28 + random.nextInt(18)));
        }
    }

    protected void paintComponent(Graphics raw) {
        super.paintComponent(raw);
        Graphics2D g = (Graphics2D) raw.create();
        g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);

        g.setPaint(new RadialGradientPaint(
                new Point2D.Double(getWidth() / 2.0, getHeight() / 2.0),
                Math.max(getWidth(), getHeight()) * .7f,
                new float[]{0, 1},
                new Color[]{new Color(22, 35, 76), new Color(3, 5, 14)}));
        g.fillRect(0, 0, getWidth(), getHeight());

        for (int i = N - 1; i > 0; i--) {
            double t = 1 - i / (double) N;
            int r = (int) (5 + 15 * t);
            Color c = Color.getHSBColor((float) (.44 + .10 * t), .82f, .95f);
            g.setColor(new Color(c.getRed(), c.getGreen(), c.getBlue(), 55));
            g.fillOval((int) x[i] - r - 5, (int) y[i] - r - 5, (r + 5) * 2, (r + 5) * 2);
            g.setColor(c);
            g.fillOval((int) x[i] - r, (int) y[i] - r, r * 2, r * 2);
        }

        Graphics2D h = (Graphics2D) g.create();
        h.translate(x[0], y[0]);
        h.rotate(angle);
        h.setColor(new Color(40, 255, 190, 65));
        h.fillOval(-32, -27, 68, 54);
        h.setColor(new Color(35, 210, 150));
        h.fillRoundRect(-25, -20, 56, 40, 22, 22);

        Path2D snout = new Path2D.Double();
        snout.moveTo(18, -12);
        snout.lineTo(40, 0);
        snout.lineTo(18, 12);
        snout.closePath();
        h.setColor(new Color(70, 240, 170));
        h.fill(snout);

        h.setColor(new Color(160, 255, 230));
        h.fillPolygon(new int[]{-12, -22, 1}, new int[]{-17, -39, -20}, 3);
        h.fillPolygon(new int[]{-12, -22, 1}, new int[]{17, 39, 20}, 3);

        double eyeH = 6 * Math.max(.12, Math.abs(Math.sin(time * .22)));
        h.setColor(Color.WHITE);
        h.fill(new Ellipse2D.Double(2, -14, 12, eyeH));
        h.fill(new Ellipse2D.Double(2, 8, 12, eyeH));
        h.setColor(new Color(10, 20, 25));
        h.fillOval(8, -13, 4, 4);
        h.fillOval(8, 9, 4, 4);
        h.dispose();

        for (Flame f : fire) {
            float age = f.life / (float) f.maxLife;
            int r = Math.max(2, (int) (18 * age));
            Color c = Color.getHSBColor(.02f + .12f * age, 1, 1);
            g.setColor(new Color(c.getRed(), c.getGreen(), c.getBlue(), (int) (210 * age)));
            g.fillOval((int) f.x - r / 2, (int) f.y - r / 2, r, r);
        }

        g.setFont(new Font(Font.SANS_SERIF, Font.BOLD, Math.max(18, getWidth() / 27)));
        g.setColor(new Color(240, 250, 255, 220));
        centre(g, "MOVE THE CURSOR", 56);
        g.setFont(new Font(Font.SANS_SERIF, Font.PLAIN, Math.max(13, getWidth() / 45)));
        g.setColor(new Color(190, 215, 235, 175));
        centre(g, "Click to breathe fire • Java Swing • 1 file", 84);
        g.dispose();
    }

    void centre(Graphics2D g, String text, int y) {
        g.drawString(text, (getWidth() - g.getFontMetrics().stringWidth(text)) / 2, y);
    }

    static class Flame {
        double x, y, vx, vy;
        int life, maxLife;

        Flame(double x, double y, double vx, double vy, int life) {
            this.x = x;
            this.y = y;
            this.vx = vx;
            this.vy = vy;
            this.life = this.maxLife = life;
        }

        void update() {
            x += vx;
            y += vy;
            vx *= .965;
            vy = vy * .965 + .025;
            life--;
        }
    }
}

How do I use SQL string, number, and date functions?

By Wayan in Learning SQL 0 Comment

SQL databases store raw values, but real reports rarely display them exactly as stored. Names may need to be capitalized, prices rounded, and dates formatted in a friendlier way. SQL provides built-in functions that transform values directly inside a query, so the database returns exactly what your application or report needs.

In this tutorial, you will learn how to use the most common SQL string, number, and date functions. You will see how each category works, when to use it, and how the syntax may differ between database systems.

Prerequisites

To follow along, you should be comfortable with:

  • Writing basic SELECT statements.
  • Filtering rows with WHERE.
  • Sorting results with ORDER BY.

You also need a running database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite) where you can create a small sample table.

Sample Database

We will continue using the online bookstore domain from earlier tutorials. For this article, a single books table is enough.

CREATE TABLE books (
    book_id      INT PRIMARY KEY,
    title        VARCHAR(200) NOT NULL,
    author_name  VARCHAR(100) NOT NULL,
    price        DECIMAL(8, 2) NOT NULL,
    published_at DATE NOT NULL
);

INSERT INTO books (book_id, title, author_name, price, published_at) VALUES
    (1, 'Clean Code',              'Robert C. Martin',   35.499, DATE '2008-08-01'),
    (2, 'Effective Java',          'Joshua Bloch',       42.000, DATE '2018-01-06'),
    (3, 'The Pragmatic Programmer','Andrew Hunt',        39.950, DATE '1999-10-20'),
    (4, 'Refactoring',             'martin fowler',      45.750, DATE '2018-11-30'),
    (5, 'Domain-Driven Design',    'Eric Evans',         50.000, DATE '2003-08-22');

Note: The literal DATE '2008-08-01' is standard SQL. In MySQL and SQL Server you can also write '2008-08-01' directly.

What Is a SQL Function?

A function takes one or more input values and returns a single output value. Functions can appear almost anywhere a value can appear: in SELECT, WHERE, ORDER BY, and so on.

SELECT UPPER(title) AS title_upper
FROM books;

There are two broad categories worth knowing early:

  • Scalar functions operate on one row at a time (the focus of this tutorial).
  • Aggregate functions such as SUM, COUNT, and AVG operate on groups of rows and are covered in a later tutorial.

String Functions

String functions transform text values. The most commonly used ones include:

Function Purpose
UPPER(text) Convert to uppercase.
LOWER(text) Convert to lowercase.
LENGTH(text) Return the number of characters.
TRIM(text) Remove leading and trailing spaces.
SUBSTRING(text FROM a FOR b) Extract part of a string.
REPLACE(text, from, to) Replace occurrences of a substring.
Concatenation Combine two or more strings.

Example: Normalize Names and Show Title Length

SELECT
    book_id,
    UPPER(title)                AS title_upper,
    LOWER(author_name)          AS author_lower,
    LENGTH(title)               AS title_length
FROM books
ORDER BY book_id;

Expected result:

book_id title_upper author_lower title_length
1 CLEAN CODE robert c. martin 10
2 EFFECTIVE JAVA joshua bloch 14
3 THE PRAGMATIC PROGRAMMER andrew hunt 24
4 REFACTORING martin fowler 11
5 DOMAIN-DRIVEN DESIGN eric evans 20

Example: Concatenate Values

String concatenation is one of the areas where SQL dialects differ the most.

Portable (ANSI SQL, PostgreSQL, Oracle Database, SQLite):

SELECT title || ' by ' || author_name AS display_label
FROM books;

MySQL and MariaDB:

SELECT CONCAT(title, ' by ', author_name) AS display_label
FROM books;

SQL Server:

SELECT title + ' by ' + author_name AS display_label
FROM books;

CONCAT(...) is also supported by PostgreSQL, SQL Server, and Oracle Database, and is often the safest choice when you want a single function name across systems.

Number Functions

Number functions perform math on numeric columns. Common ones include:

Function Purpose
ABS(number) Absolute value.
ROUND(number, digits) Round to a given number of decimals.
CEIL(number) / CEILING(number) Round up to the next integer.
FLOOR(number) Round down to the previous integer.
MOD(a, b) or a % b Remainder after division.
POWER(a, b) Raise a to the power b.

Example: Round Prices and Apply a Discount

SELECT
    book_id,
    title,
    price                              AS original_price,
    ROUND(price, 2)                    AS price_rounded,
    ROUND(price * 0.90, 2)             AS price_after_10_percent_off,
    CEIL(price)                        AS price_ceiling,
    FLOOR(price)                       AS price_floor
FROM books
ORDER BY book_id;

Expected result:

book_id title original_price price_rounded price_after_10_percent_off price_ceiling price_floor
1 Clean Code 35.499 35.50 31.95 36 35
2 Effective Java 42.000 42.00 37.80 42 42
3 The Pragmatic Programmer 39.950 39.95 35.96 40 39
4 Refactoring 45.750 45.75 41.18 46 45
5 Domain-Driven Design 50.000 50.00 45.00 50 50

Notes:

  • CEIL is called CEILING in SQL Server.
  • Rounding half-away-from-zero versus banker’s rounding may differ by database. Consult your database documentation when exact rounding rules matter, especially for money.

Date and Time Functions

Date functions extract, compute, or format temporal values. Common tasks include getting the current date, extracting a year, or computing the difference between two dates.

Frequently used functions:

Task PostgreSQL / ANSI MySQL SQL Server
Current date CURRENT_DATE CURDATE() CAST(GETDATE() AS DATE)
Current timestamp CURRENT_TIMESTAMP NOW() GETDATE() / SYSDATETIME()
Extract year EXTRACT(YEAR FROM published_at) YEAR(published_at) YEAR(published_at)
Extract month EXTRACT(MONTH FROM published_at) MONTH(published_at) MONTH(published_at)
Add days published_at + INTERVAL '7 days' DATE_ADD(published_at, INTERVAL 7 DAY) DATEADD(day, 7, published_at)
Difference in days (a - b) DATEDIFF(a, b) DATEDIFF(day, b, a)

Tip: EXTRACT is defined by the SQL standard and works in PostgreSQL, MySQL 8+, MariaDB, and Oracle Database. Prefer it when portability matters.

Example: Show Publication Year and Age in Years

Portable version using EXTRACT:

SELECT
    book_id,
    title,
    published_at,
    EXTRACT(YEAR FROM published_at)                       AS published_year,
    EXTRACT(YEAR FROM CURRENT_DATE)
        - EXTRACT(YEAR FROM published_at)                 AS age_in_years
FROM books
ORDER BY published_at;

Expected result (as of 2026):

book_id title published_at published_year age_in_years
3 The Pragmatic Programmer 1999-10-20 1999 27
5 Domain-Driven Design 2003-08-22 2003 23
1 Clean Code 2008-08-01 2008 18
2 Effective Java 2018-01-06 2018 8
4 Refactoring 2018-11-30 2018 8

Note: age_in_years computed by subtracting years is an approximation. If a book was published later in the year than today’s date, the true age is one year less. Precise age calculations require additional logic and are covered in an intermediate tutorial.

Example: Filter Books Published in the Last 10 Years

PostgreSQL:

SELECT title, published_at
FROM books
WHERE published_at >= CURRENT_DATE - INTERVAL '10 years'
ORDER BY published_at DESC;

MySQL:

SELECT title, published_at
FROM books
WHERE published_at >= DATE_SUB(CURDATE(), INTERVAL 10 YEAR)
ORDER BY published_at DESC;

SQL Server:

SELECT title, published_at
FROM books
WHERE published_at >= DATEADD(YEAR, -10, CAST(GETDATE() AS DATE))
ORDER BY published_at DESC;

Combining Functions

Functions can be nested to build more expressive queries. Combining string and date functions is a common pattern for building readable labels.

PostgreSQL / Oracle Database / SQLite:

SELECT
    UPPER(TRIM(title))
        || ' ('
        || CAST(EXTRACT(YEAR FROM published_at) AS VARCHAR(4))
        || ')' AS display_label
FROM books
ORDER BY published_at;

MySQL:

SELECT
    CONCAT(UPPER(TRIM(title)), ' (', YEAR(published_at), ')') AS display_label
FROM books
ORDER BY published_at;

Expected result (values are the same regardless of dialect):

display_label
THE PRAGMATIC PROGRAMMER (1999)
DOMAIN-DRIVEN DESIGN (2003)
CLEAN CODE (2008)
EFFECTIVE JAVA (2018)
REFACTORING (2018)

Common Mistakes

Assuming a Function Exists in Every Database

Not every database supports the same function name. For example, LEN exists in SQL Server, but the portable equivalent is LENGTH (or CHAR_LENGTH for character counts on multibyte strings). Always check the reference for your database version before assuming a function is available.

Applying a Function to an Indexed Column in WHERE

Wrapping an indexed column in a function often prevents the database from using its index efficiently.

Slower:

SELECT title
FROM books
WHERE YEAR(published_at) = 2018;

Usually faster and index-friendly:

SELECT title
FROM books
WHERE published_at >= DATE '2018-01-01'
  AND published_at <  DATE '2019-01-01';

Measure with EXPLAIN before assuming one form is always faster; execution-plan syntax and output vary between database systems.

Comparing Strings with Inconsistent Case

'Java' and 'java' may or may not compare as equal, depending on the database and the column’s collation. When case-insensitive comparisons are required, normalize both sides:

SELECT title
FROM books
WHERE LOWER(author_name) = LOWER('Martin Fowler');

Be aware that this may still bypass indexes; consider using a case-insensitive collation or a functional index when supported.

Confusing NULL Behavior

Most functions return NULL when given a NULL argument. For example, LENGTH(NULL) returns NULL, not 0. Use COALESCE to substitute a default:

SELECT COALESCE(LENGTH(author_name), 0) AS name_length
FROM books;

Database Compatibility

  • PostgreSQL: Rich set of standard-compliant functions. Supports || concatenation, EXTRACT, and INTERVAL arithmetic.
  • MySQL / MariaDB: Provide CONCAT, YEAR, MONTH, DATE_ADD, DATE_SUB, NOW, CURDATE. Note that || is logical OR unless PIPES_AS_CONCAT mode is enabled.
  • SQL Server: Uses + for string concatenation, LEN for string length, GETDATE, DATEADD, DATEDIFF. Also supports CONCAT.
  • Oracle Database: Supports || concatenation, SUBSTR, LENGTH, TO_CHAR, ADD_MONTHS, and SYSDATE.
  • SQLite: Provides a smaller set of functions. Dates are typically stored as text or numbers and manipulated with DATE, STRFTIME, and DATETIME.

When in doubt, check your database version’s official reference.

Best Practices

  • Prefer standard functions such as EXTRACT and CONCAT when they exist in your target databases.
  • Avoid wrapping indexed columns in functions inside WHERE when equivalent range predicates exist.
  • Format values in the application layer when possible; use SQL functions when the transformation belongs to the query result (for example, aggregation keys or grouping).
  • Keep expressions readable: alias every derived column with AS.
  • Be explicit about types when combining functions, especially when mixing strings, numbers, and dates.

Conclusion

You learned how to use the most common SQL string, number, and date functions to transform values directly inside a query, and how their syntax varies between PostgreSQL, MySQL, SQL Server, Oracle Database, and SQLite. Use these functions to shape query results into exactly the form your application or report needs, and remember that applying functions to indexed columns can affect performance.