MariaDB ve MySQL’de SUBSTRING ile Metin Parçalama

Tarafındankomut
0 Yorum

Veritabanlarında metin işleme söz konusu olduğunda, çoğu sysadmin’in aklına ilk gelen fonksiyonlardan biri SUBSTRING olur. Günlük işlerde log analizi yapıyor olsun, kullanıcı verilerini temizliyor olsun ya da uygulama verilerinden belirli parçaları çekiyor olsun, bu fonksiyon olmadan işlerin ne kadar zorlaşacağını düşününce değerini daha iyi anlıyorsunuz. Bu yazıda MariaDB ve MySQL üzerinde SUBSTRING fonksiyonunu her açıdan ele alacağız, gerçek dünyadan örneklerle nasıl kullanıldığını göstereceğiz.

SUBSTRING Nedir ve Neden Kullanırız

SUBSTRING fonksiyonu, bir metin değerinden belirli bir konumdan başlayarak belirli uzunlukta bir parça almanızı sağlar. Basit görünse de gerçek hayattaki kullanım senaryoları son derece geniştir. IP adreslerinin oktetlerini ayırmak, telefon numaralarından alan kodlarını çekmek, log dosyalarındaki tarih bilgilerini parse etmek, dosya uzantılarını bulmak gibi onlarca durumda bu fonksiyona ihtiyaç duyarsınız.

MariaDB ve MySQL’de SUBSTRING’in birkaç farklı yazım şekli mevcuttur:

  • SUBSTRING(str, pos): pos pozisyonundan itibaren sonuna kadar alır
  • SUBSTRING(str, pos, len): pos pozisyonundan başlayarak len karakter alır
  • SUBSTRING(str FROM pos): SQL standart sözdizimi
  • SUBSTRING(str FROM pos FOR len): SQL standart sözdizimi ile uzunluk belirtme
  • SUBSTR(): SUBSTRING ile tamamen aynı, kısaltılmış versiyonu
  • MID(): MySQL’e özgü, SUBSTRING ile aynı işlevi görür

Pozisyon değeri 1’den başlar, C gibi dillerdeki 0 tabanlı indekslemeyle karıştırmamak gerekir. Negatif değer kullanırsanız sağdan saymaya başlar, bunu da ileride örneklerle göreceğiz.

Temel SUBSTRING Kullanımı

İlk olarak en basit kullanım şekillerini bir örnek veritabanı üzerinde gösterelim. Elimizde bir kullanicilar tablosu olduğunu varsayalım.

-- Temel SUBSTRING örnekleri
SELECT SUBSTRING('Merhaba Dunya', 1, 7);
-- Sonuç: Merhaba

SELECT SUBSTRING('Merhaba Dunya', 9);
-- Sonuç: Dunya

SELECT SUBSTRING('Merhaba Dunya', -5);
-- Sonuç: Dunya

SELECT SUBSTR('[email protected]', 9);
-- Sonuç: @ornek.com

-- SQL standart sözdizimi
SELECT SUBSTRING('Linux Sunucu' FROM 7 FOR 6);
-- Sonuç: Sunucu

Negatif pozisyon kullanımına dikkat edin. -5 yazdığınızda metnin sonundan 5. karakterden başlayarak sona kadar olan kısmı alırsınız. Bu özellikle dosya uzantısı gibi sabit uzunluklu son parçaları çekerken kullanışlıdır ama metinlerin uzunluğu değişkensse dikkatli olmanız gerekir.

Tablo Sütunları Üzerinde SUBSTRING

Gerçek senaryolara geçelim. Diyelim ki bir e-ticaret sisteminde siparisler tablonuz var ve sipariş numaraları ORD-2024-00001 formatında tutuluyor. Sadece sayısal kısmı çekmek istiyorsunuz.

-- Örnek tablo ve veri
CREATE TABLE siparisler (
    id INT AUTO_INCREMENT PRIMARY KEY,
    siparis_no VARCHAR(20),
    musteri_adi VARCHAR(100),
    tutar DECIMAL(10,2),
    olusturma_tarihi DATETIME
);

INSERT INTO siparisler (siparis_no, musteri_adi, tutar, olusturma_tarihi) VALUES
('ORD-2024-00001', 'Ahmet Yilmaz', 150.00, '2024-01-15 10:30:00'),
('ORD-2024-00002', 'Mehmet Kaya', 275.50, '2024-01-15 11:45:00'),
('ORD-2024-00003', 'Ayse Demir', 89.99, '2024-01-16 09:15:00');

-- Sipariş numarasından sadece sayısal kısmı çek
SELECT
    siparis_no,
    SUBSTRING(siparis_no, 10) AS sira_no,
    SUBSTRING(siparis_no, 5, 4) AS yil,
    musteri_adi
FROM siparisler;

-- Sonuçlar:
-- ORD-2024-00001 | 00001 | 2024 | Ahmet Yilmaz
-- ORD-2024-00002 | 00002 | 2024 | Mehmet Kaya
-- ORD-2024-00003 | 00003 | 2024 | Ayse Demir

Bu sorgu gayet güzel çalışıyor ama formatlı metin içinde dinamik konumlara ihtiyaç duyduğunuzda ne olacak? İşte burada SUBSTRING’i diğer fonksiyonlarla birlikte kullanmak zorunda kalıyorsunuz.

SUBSTRING ve LOCATE ile Dinamik Pozisyon Belirleme

LOCATE fonksiyonu bir karakter veya metnin başka bir metin içinde kaçıncı pozisyonda olduğunu döndürür. Bu ikiliyi birleştirince çok daha esnek sorgular yazabilirsiniz.

-- E-posta adreslerinden kullanıcı adı ve domain ayırmak
CREATE TABLE kullanicilar (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150),
    kayit_tarihi DATE
);

INSERT INTO kullanicilar (email, kayit_tarihi) VALUES
('[email protected]', '2024-01-10'),
('[email protected]', '2024-01-11'),
('[email protected]', '2024-01-12'),
('[email protected]', '2024-01-13');

-- @ işaretinden önceki kullanıcı adını çek
SELECT
    email,
    SUBSTRING(email, 1, LOCATE('@', email) - 1) AS kullanici_adi,
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM kullanicilar;

-- Sonuçlar:
-- [email protected]    | ahmet.yilmaz | ornek.com
-- [email protected]         | m.kaya       | sirket.net
-- [email protected]       | ayse_demir   | test.org
-- info@uzun-domain-adi...   | info         | uzun-domain-adi.com.tr

LOCATE ile gelen pozisyondan 1 çıkardığımıza dikkat edin, böylece @ karakterinin kendisini dahil etmemiş oluyoruz. Domain tarafında ise @ pozisyonuna 1 ekleyerek başlatıyoruz.

Log Analizi Senaryosu

Sysadmin olarak en sık karşılaştığımız durumlardan biri log verilerini veritabanında saklamak ve analiz etmek. Apache veya Nginx access log’larını MySQL’e aktarıp sorguladığınızı düşünün.

-- Web sunucu log tablosu
CREATE TABLE access_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_satirı VARCHAR(500),
    kayit_zamani TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO access_logs (log_satiri) VALUES
('192.168.1.100 - - [15/Jan/2024:10:30:45 +0300] "GET /index.html HTTP/1.1" 200 1234'),
('10.0.0.55 - admin [15/Jan/2024:10:31:02 +0300] "POST /api/login HTTP/1.1" 401 89'),
('172.16.0.200 - - [15/Jan/2024:10:31:15 +0300] "GET /images/logo.png HTTP/1.1" 200 5678');

-- IP adresini log satırından çek
SELECT
    log_satiri,
    SUBSTRING(log_satiri, 1, LOCATE(' ', log_satiri) - 1) AS ip_adresi,
    SUBSTRING(
        log_satiri,
        LOCATE('[', log_satiri) + 1,
        LOCATE(']', log_satiri) - LOCATE('[', log_satiri) - 1
    ) AS tarih_saat,
    SUBSTRING(
        log_satiri,
        LOCATE('"', log_satiri) + 1,
        LOCATE('"', log_satiri, LOCATE('"', log_satiri) + 1) - LOCATE('"', log_satiri) - 1
    ) AS http_istek
FROM access_logs;

Bu sorgu biraz karmaşık görünebilir ama satır satır incelersek mantığını kolayca anlayabiliriz. LOCATE ile köşeli parantez ve tırnak işaretlerinin konumlarını bulup SUBSTRING ile aralarındaki kısmı kesip alıyoruz.

IP Adreslerini Parçalara Ayırmak

Ağ yönetimi yapanlar için klasik bir senaryo: IP adreslerini oktetlerine ayırmak. SUBSTRING_INDEX de bu iş için kullanılsa da, SUBSTRING ile nasıl yapıldığını bilmek önemli.

-- IP adresi oktetlerini SUBSTRING ve LOCATE ile ayırma
CREATE TABLE sunucular (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sunucu_adi VARCHAR(50),
    ip_adresi VARCHAR(15),
    rol VARCHAR(30)
);

INSERT INTO sunucular (sunucu_adi, ip_adresi, rol) VALUES
('web-01', '192.168.10.15', 'Web Sunucu'),
('db-01', '192.168.10.50', 'Veritabani'),
('proxy-01', '10.0.0.1', 'Proxy'),
('backup-01', '172.16.5.100', 'Yedekleme');

-- İlk iki okteti network adresi olarak çek
SELECT
    sunucu_adi,
    ip_adresi,
    -- İlk oktet
    SUBSTRING(ip_adresi, 1, LOCATE('.', ip_adresi) - 1) AS oktet1,
    -- İkinci oktet
    SUBSTRING(
        ip_adresi,
        LOCATE('.', ip_adresi) + 1,
        LOCATE('.', ip_adresi, LOCATE('.', ip_adresi) + 1) - LOCATE('.', ip_adresi) - 1
    ) AS oktet2,
    -- /24 network adresi
    CONCAT(
        SUBSTRING(ip_adresi, 1, LOCATE('.', ip_adresi, LOCATE('.', ip_adresi) + 1)),
        '0/24'
    ) AS network
FROM sunucular;

-- Aynı ağdaki sunucuları bulmak
SELECT
    s1.sunucu_adi,
    s1.ip_adresi,
    s2.sunucu_adi AS ayni_agdaki_sunucu
FROM sunucular s1
JOIN sunucular s2 ON
    SUBSTRING(s1.ip_adresi, 1, LOCATE('.', s1.ip_adresi, LOCATE('.', s1.ip_adresi) + 1)) =
    SUBSTRING(s2.ip_adresi, 1, LOCATE('.', s2.ip_adresi, LOCATE('.', s2.ip_adresi) + 1))
    AND s1.id != s2.id;

Bu örnekte LOCATE’i iç içe kullanarak ikinci noktanın konumunu buluyoruz. İlk LOCATE ile birinci noktayı, sonra bu pozisyondan sonra arama yaparak ikinci noktayı buluyoruz.

SUBSTRING ile Veri Temizleme ve Dönüştürme

Üretim ortamında sıkça karşılaşılan bir durum: Eski sistemden gelen verilerin temizlenmesi. Diyelim ki telefon numaraları tutarsız formatlarda girilmiş.

-- Telefon numarası temizleme örneği
CREATE TABLE musteriler (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ad_soyad VARCHAR(100),
    telefon VARCHAR(20)
);

INSERT INTO musteriler (ad_soyad, telefon) VALUES
('Ahmet Yilmaz', '+90-532-123-4567'),
('Mehmet Kaya', '0(533)456-7890'),
('Ayse Demir', '+905321234567'),
('Fatma Celik', '05441234567');

-- Alan kodunu çek (farklı formatlar için birleşik sorgu)
SELECT
    ad_soyad,
    telefon,
    CASE
        WHEN telefon LIKE '+90-%' THEN SUBSTRING(telefon, 5, 3)
        WHEN telefon LIKE '0(%' THEN SUBSTRING(telefon, 3, 3)
        WHEN telefon LIKE '+90%' THEN SUBSTRING(telefon, 4, 3)
        WHEN telefon LIKE '0%' THEN SUBSTRING(telefon, 2, 3)
    END AS alan_kodu,
    -- Sadece rakamları bırakan temizlenmiş numara (kademeli yaklaşım)
    CASE
        WHEN telefon LIKE '+90%' THEN CONCAT('0', SUBSTRING(REPLACE(REPLACE(REPLACE(telefon, '-', ''), '(', ''), ')', ''), 4))
        ELSE REPLACE(REPLACE(REPLACE(telefon, '-', ''), '(', ''), ')', '')
    END AS temiz_telefon
FROM musteriler;

Tarih ve Zaman Verilerini Parse Etmek

Zaman zaman tarih bilgileri VARCHAR olarak saklanmış veritabanlarıyla karşılaşırsınız. Bu verileri doğru şekilde parse etmek için SUBSTRING oldukça işlevsel.

-- String formatında tutulan tarih verilerini işleme
CREATE TABLE etkinlikler (
    id INT AUTO_INCREMENT PRIMARY KEY,
    etkinlik_adi VARCHAR(100),
    tarih_str VARCHAR(20)  -- '20240115143022' formatında
);

INSERT INTO etkinlikler (etkinlik_adi, tarih_str) VALUES
('Yedekleme Basladı', '20240115143022'),
('Yedekleme Tamamlandı', '20240115151845'),
('Sistem Yeniden Baslatıldı', '20240116023015'),
('Güncelleme Uygulandı', '20240116034530');

-- Tarih parçalarını ayır ve gerçek DATETIME'a dönüştür
SELECT
    etkinlik_adi,
    tarih_str,
    SUBSTRING(tarih_str, 1, 4) AS yil,
    SUBSTRING(tarih_str, 5, 2) AS ay,
    SUBSTRING(tarih_str, 7, 2) AS gun,
    SUBSTRING(tarih_str, 9, 2) AS saat,
    SUBSTRING(tarih_str, 11, 2) AS dakika,
    SUBSTRING(tarih_str, 13, 2) AS saniye,
    STR_TO_DATE(tarih_str, '%Y%m%d%H%i%s') AS gercek_datetime,
    -- Okunabilir format
    CONCAT(
        SUBSTRING(tarih_str, 7, 2), '/',
        SUBSTRING(tarih_str, 5, 2), '/',
        SUBSTRING(tarih_str, 1, 4), ' ',
        SUBSTRING(tarih_str, 9, 2), ':',
        SUBSTRING(tarih_str, 11, 2), ':',
        SUBSTRING(tarih_str, 13, 2)
    ) AS okunabilir_tarih
FROM etkinlikler
ORDER BY tarih_str;

-- İki etkinlik arasındaki süreyi hesapla
SELECT
    e1.etkinlik_adi AS baslangic,
    e2.etkinlik_adi AS bitis,
    TIMESTAMPDIFF(
        MINUTE,
        STR_TO_DATE(e1.tarih_str, '%Y%m%d%H%i%s'),
        STR_TO_DATE(e2.tarih_str, '%Y%m%d%H%i%s')
    ) AS sure_dakika
FROM etkinlikler e1
JOIN etkinlikler e2 ON e2.id = e1.id + 1;

Performans Konuları

SUBSTRING kullanırken dikkat etmeniz gereken bazı performans noktaları var. WHERE koşulunda SUBSTRING kullanmak indeksleri bypass edebilir, bu ciddi bir performans sorununa yol açar.

-- KÖTÜ: İndeks kullanamaz, full table scan yapar
SELECT * FROM kullanicilar
WHERE SUBSTRING(email, 1, 5) = 'ahmet';

-- İYİ: LIKE ile indeks kullanabilir (prefix indeksi varsa)
SELECT * FROM kullanicilar
WHERE email LIKE 'ahmet%';

-- KÖTÜ: Her satır için hesaplama yapılır
SELECT * FROM siparisler
WHERE SUBSTRING(siparis_no, 5, 4) = '2024';

-- İYİ: Sabit prefix pattern ile çalışmak
SELECT * FROM siparisler
WHERE siparis_no LIKE 'ORD-2024-%';

-- Eğer computed column kullanma imkanınız varsa (MariaDB 5.2+)
ALTER TABLE kullanicilar
ADD COLUMN email_domain VARCHAR(100)
GENERATED ALWAYS AS (SUBSTRING(email, LOCATE('@', email) + 1)) STORED;

CREATE INDEX idx_email_domain ON kullanicilar(email_domain);

-- Artık bu sorgu indeks kullanır
SELECT * FROM kullanicilar WHERE email_domain = 'ornek.com';

Generated/Computed Column kullanımı bu tür durumlarda harika bir çözüm. Sütunu bir kez tanımlıyorsunuz, MariaDB otomatik hesaplıyor ve indeksleyebiliyorsunuz.

Gerçek Dünya: Uygulama Log Analizi

Son olarak daha kapsamlı bir senaryo ele alalım. Bir uygulama hata loglarını veritabanında saklıyor ve bu loglar üzerinde analiz yapmamız gerekiyor.

-- Uygulama log tablosu
CREATE TABLE uygulama_loglari (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    log_metni TEXT,
    olusturma_zamani TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_zaman (olusturma_zamani)
);

INSERT INTO uygulama_loglari (log_metni) VALUES
('[ERROR][2024-01-15 14:30:22][AuthService] Kullanici girisi basarisiz: user_id=1042'),
('[WARN][2024-01-15 14:31:05][DatabasePool] Baglanti havuzu dolmak uzere: pool_size=95/100'),
('[ERROR][2024-01-15 14:31:48][PaymentService] Odeme islemi hatasi: order_id=5521, code=ERR_TIMEOUT'),
('[INFO][2024-01-15 14:32:10][CacheService] Cache temizlendi: keys_removed=1523'),
('[ERROR][2024-01-15 14:33:02][AuthService] Kullanici girisi basarisiz: user_id=1042');

-- Log seviyesini, zamanını, servisini ve mesajını ayır
SELECT
    id,
    -- Log seviyesi: [ERROR] -> ERROR
    SUBSTRING(
        log_metni,
        2,
        LOCATE(']', log_metni) - 2
    ) AS log_seviyesi,
    -- Zaman bilgisi
    SUBSTRING(
        log_metni,
        LOCATE('[', log_metni, 2) + 1,
        19
    ) AS log_zamani,
    -- Servis adı
    SUBSTRING(
        log_metni,
        LOCATE('[', log_metni, LOCATE(']', log_metni, LOCATE(']', log_metni) + 1) + 1) + 1,
        LOCATE(']', log_metni, LOCATE('[', log_metni, LOCATE(']', log_metni, LOCATE(']', log_metni) + 1) + 1) + 1) -
        LOCATE('[', log_metni, LOCATE(']', log_metni, LOCATE(']', log_metni) + 1) + 1) - 1
    ) AS servis,
    olusturma_zamani
FROM uygulama_loglari
ORDER BY id;

-- ERROR loglarını say ve servise göre grupla
SELECT
    SUBSTRING(
        log_metni,
        2,
        LOCATE(']', log_metni) - 2
    ) AS log_seviyesi,
    COUNT(*) AS adet,
    MIN(olusturma_zamani) AS ilk_gorulme,
    MAX(olusturma_zamani) AS son_gorulme
FROM uygulama_loglari
WHERE log_metni LIKE '[ERROR]%'
GROUP BY log_seviyesi;

Iç içe LOCATE kullanımı karmaşık görünse de aynı log formatına sahip milyonlarca kaydı saniyeler içinde analiz edebilirsiniz. Üretimde bu tür sorgular için mutlaka EXPLAIN ile sorgu planını kontrol edin.

SUBSTRING ile Birlikte Sık Kullanılan Fonksiyonlar

Pratik çalışmada SUBSTRING nadiren yalnız kullanılır. Yanında sıklıkla göreceğiniz fonksiyonlar şunlardır:

  • LOCATE(substr, str): Alt metnin pozisyonunu bulur, SUBSTRING ile dinamik parçalama yapar
  • LENGTH(str): Byte cinsinden uzunluk, ikili veriler için kullanılır
  • CHAR_LENGTH(str): Karakter cinsinden uzunluk, UTF-8 veriler için doğru seçim
  • CONCAT(str1, str2): Parçaları birleştirmek için
  • TRIM(str): Başından ve sonundan boşluk temizler
  • REPLACE(str, from, to): Belirli karakterleri değiştirmek için
  • UPPER(str) / LOWER(str): Büyük/küçük harf dönüşümü
  • REGEXP_SUBSTR(str, pattern): MariaDB 10.0.5+ ile regex tabanlı parçalama, karmaşık yapılar için daha güçlü alternatif

Sonuç

SUBSTRING, veritabanı yönetiminde görünürde basit ama uygulamada son derece güçlü bir fonksiyondur. E-posta adreslerini parçalamaktan log analizine, IP adresi işlemekten veri migrasyon temizliklerine kadar pek çok günlük sysadmin görevinde işinize yarar.

Dikkat etmeniz gereken en önemli nokta performanstır. WHERE koşulunda SUBSTRING kullanmak indeksleri devre dışı bırakır. Bu durumda generated column oluşturup indekslemek ya da sorguyu LIKE ile yeniden yazmak doğru yaklaşımdır. EXPLAIN çıktısını her zaman inceleyin, özellikle büyük tablolarda full table scan görüyorsanız sorgunuzu gözden geçirin.

Karmaşık metin parsing işleri için MariaDB 10.0.5 ve üzerinde REGEXP_SUBSTR fonksiyonunu da değerlendirin. Bazı durumlar için daha okunabilir ve esnek sorgular yazmanıza imkan tanır. Ama saf SUBSTRING ve LOCATE kombinasyonu, eski sürümlerle uyumluluk gerektiren ortamlarda her zaman güvenilir bir seçenek olarak yerini korumaktadır.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir