MySQL State Names and Codes Database

Download a Complete SQL Table of Country-Region State/Province Names and Codes

Our SQL database provides a structured table containing the names and codes for states and provinces worldwide, essential for developers and data managers working with global datasets.

This database includes approximately 5,000 entries, featuring state or province names, their respective codes, and associated country codes. For brevity, only a preview of the data is shown here. The complete dataset is available for download via the button below.

SQL Table Structure

The database table is pre-designed for easy integration into MySQL systems. Below is the structure of the table

SQL

-- Create states table with improved structure and constraints
CREATE TABLE IF NOT EXISTS `states` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `state_name` VARCHAR(255) NOT NULL,
    `state_code` VARCHAR(100) NOT NULL,
    `country_code` CHAR(2) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_state_country` (`state_code`, `country_code`),
    INDEX `idx_country_code` (`country_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table `states`
INSERT INTO `states` (`state_name`, `state_code`, `country_code`) VALUES
('Badakhshan', 'BDS', 'AF'),
('Badghis', 'BDG', 'AF'),
('Baghlan', 'BGL', 'AF'),
('Balkh', 'BAL', 'AF'),
('Bamyan', 'BAM', 'AF'),
('Daykundi', 'DAY', 'AF'),
('Farah', 'FRA', 'AF'),
('Faryab', 'FYB', 'AF'),
('Ghazni', 'GHA', 'AF'),
('Ghōr', 'GHO', 'AF');

This is only a preview. Download the full table to access all entries, ready for direct integration into your database.

Download State names Code SQL

SQL Query Example for State Names

Easily retrieve a list of state or province names and their corresponding codes for a specific country by using its country code.

The following SQL query demonstrates how to select the 'state_name' and 'state_code' columns from the 'states' table based on a given 'country_code'.

SQL

-- Query for states in India (country code: "IN")
SELECT `state_name`, `state_code`
FROM `states`
WHERE `country_code` = 'IN';

SQL

-- Query for states in the United States (country code: "US")
SELECT `state_name`, `state_code`
FROM `states`
WHERE `country_code` = 'US';

This method allows for quick and precise filtering, making it easy to retrieve state-level data for any country in the dataset.

Report Problem