SQLite String Fonksiyonları: SUBSTR, REPLACE, INSTR

Stringler, Gerçek Sorguların Yaşadığı Yerdir

Sayılarla iş kolay. Asıl mesele stringlerde başlıyor: başında sonunda boşluk olan isimler, büyük-küçük harfi karışmış e-postalar, tirelerle birbirine yapıştırılmış ID'ler, neredeyse eşleşen ama tam tutmayan serbest metin alanları... SQLite, bunların büyük çoğunluğunu uygulama tarafına hiç dokunmadan halledebileceğin küçük ama derli toplu bir string fonksiyon seti sunuyor.

Bu sayfada en sık ihtiyaç duyacağın sqlite string fonksiyonlarını ele alacağız: birleştirme, parçalama, arama, değiştirme, kırpma ve biçimlendirme.

SQLite'ta String Birleştirme: CONCAT Değil, || Operatörü

SQLite'ta CONCAT fonksiyonu yok. Stringleri birleştirmek için || operatörünü kullanıyoruz:

SELECT 'Merhaba, ' || 'dünya!' AS greeting;

SELECT 'Kullanıcı #' || 42 || ' katıldı' AS message;

Sayılar ve diğer veri tipleri otomatik olarak metne dönüştürülür. Ama dikkat edilmesi gereken bir nokta var: Operandlardan herhangi biri NULL olursa, tüm ifade NULL döner. Bu, standart SQL davranışıdır ama çoğu kişiyi şaşırtır:

SELECT 'Merhaba, ' || NULL || '!' AS greeting;

SELECT 'Merhaba, ' || COALESCE(NULL, 'arkadaş') || '!' AS safe_greeting;

COALESCE(col, '') veya COALESCE(col, 'default') ile sarmaladığınızda, eksik bir değer yüzünden tüm string'in boşa çıkmasının önüne geçersiniz.

Length, Upper ve Lower

Sürekli elinizin altında olacak üç fonksiyon:

SELECT
    LENGTH('özgeçmiş')      AS char_count,
    UPPER('merhaba dünya')  AS upper_case,
    LOWER('MERHABA DÜNYA')  AS lower_case;

LENGTH, metnin karakter sayısını döndürür, bayt sayısını değil. Eğer gerçekten bayt cinsinden uzunluk lazımsa (nadiren gerekir ama depolama analizi için işe yarar) OCTET_LENGTH kullanmalısınız. UPPER ve LOWER ise varsayılan olarak yalnızca ASCII harfleri dönüştürür; ICU eklentisini yüklemediğiniz sürece aksanlı karakterler olduğu gibi geçer.

SUBSTR: String Parçalama

SUBSTR(text, start, length) bir stringden parça koparmaya yarar. Burada dikkat edilmesi gereken şey: indeksler 1'den başlar — yani ilk karakter 0 değil, 1:

SELECT
    SUBSTR('codecademy', 1, 4)  AS first_four,
    SUBSTR('codecademy', 5)     AS rest,
    SUBSTR('codecademy', -3)    AS last_three;

Aklınızda bulunsun:

• Üçüncü argüman opsiyoneldir. Onu vermezseniz, start konumundan stringin sonuna kadar her şeyi alırsınız.
• Negatif bir start değeri, stringin sonundan itibaren saymaya başlar.
• start stringin sonunu aşıyorsa, hata almak yerine boş bir string dönersiniz.

Başka bir veritabanından gelen kas hafızanız varsa diye, SUBSTRING de eş anlamlı olarak kabul ediliyor.

INSTR ile Arama: Substring Bulma

INSTR(haystack, needle), needle ifadesinin haystack içindeki ilk geçtiği konumu 1 tabanlı olarak döner; bulamazsa 0 döner:

SELECT
    INSTR('user@example.com', '@')        AS at_position,
    INSTR('user@example.com', 'xyz')      AS not_found,
    SUBSTR('user@example.com',
           1,
           INSTR('user@example.com', '@') - 1) AS local_part;

Yukarıdaki ifade, SQLite'ta "@ işaretinden önceki her şeyi al" demenin klasik yoludur: önce INSTR ile ayraçın yerini bul, ardından SUBSTR ile dilimle. Bu ikiliyi sık sık birlikte kullanacaksınız. Dikkat etmeniz gereken bir nokta var: INSTR eşleşme bulamadığında 0 döner; dolayısıyla dilimleme öncesi bunu kontrol etmek şart. Aksi halde SUBSTR'ye 0 verirsiniz ve sessiz sedasız tuhaf sonuçlar alırsınız.

REPLACE: Bir Alt Dizeyi Başkasıyla Değiştirme

REPLACE(text, old, new) fonksiyonu, old değerinin tüm geçtiği yerleri new ile değiştirir:

SELECT
    REPLACE('555-123-4567', '-', '')                AS digits_only,
    REPLACE('Merhaba, dünya. Merhaba, tekrar.',
            'Merhaba',
            'Selam')                                AS shortened,
    REPLACE('  boşluklu  metin  ', ' ', '')         AS no_spaces;

Büyük/küçük harfe duyarlıdır ve regex kabul etmez — yalnızca düz bir alt dizgeyle çalışır. Daha karmaşık dönüşümler için REPLACE çağrılarını iç içe geçirebilirsiniz; ama iki üç seviyeyi aştığınız anda işi uygulama tarafında halletmenin zamanı gelmiş demektir.

TRIM, LTRIM, RTRIM

Kullanıcının girdiği veriler genelde başında ve sonunda boşluklarla gelir. TRIM bu boşlukları temizler:

SELECT
    TRIM('   merhaba   ')              AS both_sides,
    LTRIM('   merhaba   ')             AS left_only,
    RTRIM('   merhaba   ')             AS right_only,
    TRIM('---merhaba---', '-')         AS custom_chars;

Varsayılan olarak boşlukları kırpar. Hangi karakterlerin temizleneceğini belirtmek için ikinci bir argüman verebilirsiniz — buradaki her karakter, birebir aranan bir alt dize olarak değil, "silinecek karakterler kümesinin" bir üyesi olarak değerlendirilir. Yani TRIM('xxxhelloxx', 'x') sonucu 'hello' döner.

printf: Sayı ve String Biçimlendirme

Sabit ondalık basamak, sıfırla doldurulmuş sayı ya da hex çıktı gibi biçimlendirilmiş bir string'e ihtiyacın olduğunda devreye printf (diğer adıyla format) giriyor:

SELECT
    printf('%.2f',         3.14159)            AS two_decimals,
    printf('%05d',         42)                 AS padded,
    printf('$%,.2f',       1234567.89)         AS money,
    printf('%s, %d yaşında', 'Ada',  30)       AS sentence;

Format belirteçleri C dilinin geleneğini izler: yani %d, %s, %f, %x, 0 veya boşlukla doldurma gibi tanıdık şeyler. || operatörüyle string birleştirip üstüne bir sürü CAST atmaktan çok daha temiz bir yöntem.

LIKE vs GLOB: Desen eşleştirme farkı

İki operatör, iki ayrı dünya.

LIKE, SQL'in klasik joker karakterlerini kullanır — herhangi bir karakter dizisi için %, tek bir karakter için _ — ve ASCII karakterlerde büyük/küçük harf duyarsızdır:

SELECT 'Alice'  LIKE 'a%'      AS alice_match;
SELECT 'README' LIKE '_____'   AS five_chars;
SELECT 'foo.txt' LIKE '%.txt'  AS is_text_file;

GLOB, Unix kabuk joker karakterlerini kullanır: herhangi bir karakter dizisi için *, tek bir karakter için ? ve karakter sınıfları için [abc]. Ayrıca büyük/küçük harfe duyarlıdır:

SELECT 'Alice'  GLOB 'A*'         AS alice_match;
SELECT 'Alice'  GLOB 'a*'         AS lowercase_match;
SELECT 'log7'   GLOB 'log[0-9]'   AS digit_class;

Hangisini ne zaman seçeceğini şöyle düşün: insan tarzı "şununla başlar", "şunu içerir", "şununla biter" eşleştirmeleri için LIKE. Büyük/küçük harf duyarlılığı önemliyse veya karakter sınıflarına ihtiyacın varsa GLOB. İkisi de indeks kullanabilir ama yalnızca desen baştan sabitlenmişse ('foo%', '%foo' değil) — başta joker karakter olduğunda SQLite tüm tabloyu taramak zorunda kalır.

String Bölme: SQLite'ta SPLIT Yok

SQLite, hazır bir SPLIT_STRING fonksiyonuyla gelmiyor. Pratikte kullanabileceğin iki yol var:

-- INSTR + SUBSTR kullanarak tek bir parça çekme
WITH addresses AS (
    SELECT 'rosa@example.com'   AS email UNION ALL
    SELECT 'boris@coddy.tech'   UNION ALL
    SELECT 'ada@lovelace.org'
)
SELECT
    SUBSTR(email, 1, INSTR(email, '@') - 1)  AS local_part,
    SUBSTR(email, INSTR(email, '@') + 1)     AS domain
FROM addresses;

Bir sınırlayıcıya göre satırlara bölmek için en temiz yol, JSON dizisi üzerinde json_each kullanmak ya da özyinelemeli (recursive) bir CTE yazmaktır. Bu iki yaklaşımı ilerleyen bölümlerde ele alacağız — şimdilik bilmeniz gereken şu: "bana her kelimeyi ver" SQLite'ta tek satırlık bir iş değil.

Uygulamalı Örnek: İsimleri Temizlemek

Şimdi öğrendiklerimizi birleştirelim. Düşünün ki elinizde bir users tablosu var ve görünen isimler oldukça dağınık: fazladan boşluklar, karışık büyük-küçük harfler, bir de temizlemek istediğiniz "Dr. " veya "Mr. " gibi unvan ön ekleri var:

WITH users(id, display_name) AS (
    VALUES
      (1, '  Dr. Ada Lovelace  '),
      (2, 'mr. Boris Karloff'),
      (3, 'Rosa  Parks'),
      (4, '   alan TURING')
)
SELECT
    id,
    display_name AS original,
    TRIM(
      REPLACE(
        REPLACE(LOWER(TRIM(display_name)), 'dr. ', ''),
        'mr. ', ''
      )
    ) AS cleaned
FROM users;

İfadeyi içten dışa okuyun: önce baştaki/sondaki boşlukları temizleyin, küçük harfe çevirin, unvanları kaldırın ve unvan silindikten sonra başta boşluk kalma ihtimaline karşı bir kez daha TRIM uygulayın. Her adım tek başına basit bir fonksiyon — karmaşıklık, bunları üst üste yığmaktan geliyor. Yığın üç-dört seviyeyi aştığında ya bir generated column'a geçmek (Bölüm: Gelişmiş Özellikler) ya da temizliği veri yükleme sırasında yapmak gerektiğinin işaretidir.

Akılda Kalması Gerekenler

• Birleştirme için ||; NULL sonucu zehirlediği için COALESCE kullanın.
• SUBSTR ve INSTR ikilisi "bul ve dilimle" senaryolarının çoğunu karşılar.
• REPLACE, verilen alt dizenin tüm geçtiği yerleri değiştirir.
• TRIM ve akrabaları yalnız boşluk değil, istediğiniz karakter kümesini de temizler.
• Biçimli çıktı için doğru araç printf.
• Büyük/küçük harf duyarsız SQL joker karakterleri için LIKE, shell tarzı ve büyük/küçük harfe duyarlı kalıplar için GLOB.

Sırada: Sayısal Fonksiyonlar

Stringleri hallettik, doğal duraktaki sıra sayılarda — yuvarlama, mutlak değer, bölme tuhaflıkları ve SQLite'ın son sürümlerde eklediği matematik fonksiyonları. Hepsi bir sonraki sayfada.