Common SQL string functions and cross-database syntax differences

-- Quick overview using National Bank of India account data
SELECT
    account_id,
    UPPER(holder_name) AS name_upper,
    TRIM(holder_name) AS name_trimmed,
    LENGTH(holder_name) AS name_length,
    SUBSTRING(account_id FROM 1 FOR 3) AS account_prefix,
    LPAD(CAST(account_number AS VARCHAR), 10, '0') AS padded_number
FROM accounts
WHERE status = 'ACTIVE';

-- PostgreSQL / Oracle: double-pipe operator
SELECT account_id || ' : ' || holder_name AS display_label
FROM accounts;
-- Result: 'SAV2001 : Rajesh Kumar'

-- MySQL: CONCAT function
SELECT CONCAT(account_id, ' : ', holder_name) AS display_label
FROM accounts;
-- Result: 'SAV2001 : Rajesh Kumar'

-- MySQL gotcha: || is logical OR, not concatenation
SELECT 'SAV' || '2001';  -- Returns 0 (logical OR of two strings)
SELECT CONCAT('SAV', '2001');  -- Returns 'SAV2001'

-- SQL standard (PostgreSQL)
SELECT SUBSTRING(account_id FROM 1 FOR 3) AS prefix
FROM accounts;
-- Result: 'SAV'

-- MySQL / Oracle shorthand
SELECT SUBSTR(account_id, 1, 3) AS prefix
FROM accounts;
-- Result: 'SAV'

-- Account masking: show only the last 4 characters
SELECT 'XXXX' || RIGHT(account_id, 4) AS masked_id
FROM accounts;
-- Result: 'XXXX2001'

-- MySQL: LENGTH vs CHAR_LENGTH for multi-byte strings
SELECT LENGTH('Rajesh') AS byte_len,
       CHAR_LENGTH('Rajesh') AS char_len;
-- byte_len: 6, char_len: 6 (ASCII, same result)

-- With multi-byte character (Hindi)
SELECT LENGTH('राजेश') AS byte_len,
       CHAR_LENGTH('राजेश') AS char_len;
-- byte_len: 15 (3 bytes per character in UTF-8)
-- char_len: 5  (actual character count)

-- PostgreSQL: LENGTH always counts characters
SELECT LENGTH('राजेश');  -- Returns 5

-- Clean up imported account holder names
SELECT
    holder_name AS raw_name,
    TRIM(holder_name) AS trimmed,
    LTRIM(holder_name) AS left_trimmed,
    RTRIM(holder_name) AS right_trimmed
FROM imported_accounts;

-- SQL standard syntax
SELECT TRIM(BOTH ' ' FROM '  Rajesh Kumar  ');
-- Result: 'Rajesh Kumar'

SELECT TRIM(LEADING '0' FROM '000042');
-- Result: '42'

-- Normalize holder name for comparison
SELECT UPPER(holder_name) AS normalized_name
FROM accounts
WHERE UPPER(holder_name) = 'RAJESH KUMAR';

-- Replace characters in account display
SELECT REPLACE(account_id, 'SAV', 'SAVINGS-') AS formatted_id
FROM accounts;
-- 'SAV2001' becomes 'SAVINGS-2001'

-- Remove hyphens from imported PAN numbers
SELECT REPLACE(pan_number, '-', '') AS clean_pan
FROM customers;

-- Zero-pad account numbers to 10 digits
SELECT LPAD(CAST(account_number AS VARCHAR), 10, '0') AS padded
FROM accounts;
-- 2001 becomes '0000002001'

-- Generate a fixed-width transaction reference
SELECT 'TXN' || LPAD(CAST(txn_id AS VARCHAR), 12, '0') AS txn_ref
FROM transactions;
-- TXN000000001234

-- Right-pad branch name for a fixed-width report
SELECT RPAD(branch_name, 20, '.') AS formatted_branch
FROM branches;
-- 'DELHI..............'

-- POSITION (SQL standard / PostgreSQL)
SELECT POSITION('@' IN email) AS at_position
FROM customers;
-- 'rajesh@nbi.com' returns 7

-- INSTR (MySQL / Oracle)
SELECT INSTR(email, '@') AS at_position
FROM customers;

-- LEFT and RIGHT
SELECT LEFT(account_id, 3) AS prefix,   -- 'SAV'
       RIGHT(account_id, 4) AS suffix    -- '2001'
FROM accounts;

-- Oracle alternative: SUBSTR with negative offset for RIGHT
SELECT SUBSTR(account_id, -4) AS suffix FROM accounts;
-- Returns '2001'

-- REVERSE
SELECT REVERSE('SAV2001');  -- '1002VAS'

-- Wrong: full table scan despite index on holder_name
SELECT * FROM accounts
WHERE UPPER(holder_name) = 'RAJESH KUMAR';
-- The index on holder_name cannot be used

-- Fix 1: Functional index
CREATE INDEX idx_holder_upper ON accounts (UPPER(holder_name));
-- Now UPPER(holder_name) queries use the index

-- Fix 2: Store a normalized column
ALTER TABLE accounts ADD COLUMN holder_name_upper VARCHAR
    GENERATED ALWAYS AS (UPPER(holder_name)) STORED;
CREATE INDEX idx_holder_norm ON accounts (holder_name_upper);

-- Fix 3: Case-insensitive collation (PostgreSQL)
-- CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2');
-- ALTER TABLE accounts ALTER COLUMN holder_name TYPE VARCHAR COLLATE ci;

package com.bank.service;

import java.sql.*;
import java.util.logging.Logger;

public class AccountDisplayService {

    private static final Logger LOG = Logger.getLogger(
        AccountDisplayService.class.getName());

    private final Connection connection;

    public AccountDisplayService(Connection connection) {
        this.connection = connection;
    }

    public String getMaskedDisplay(String accountId) throws SQLException {
        String sql = "SELECT 'XXXX' || RIGHT(account_id, 4) || ' : ' "
                   + "|| UPPER(TRIM(holder_name)) AS display "
                   + "FROM accounts WHERE account_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, accountId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getString("display");
                }
            }
        }
        return "UNKNOWN";
    }
}

Output:
XXXX2001 : RAJESH KUMAR