String funksiyalar — CONCAT, LENGTH, SUBSTRING, REPLACE, TRIM

~15 daq · MySQL · Funksiyalar

Muammodan boshlaylik

Mijozlar bazasi eski Excel dan import qilindi. Natija:

  • Ismlar: " aziz KARIMOV ", "MALIKA rahimova"
  • Telefonlar: "+998 90 111-22-33", "90 1112233", "998901112233"
  • Emaillar: "Aziz@Mail.UZ"

Ularni tozalash, bir xil formatga keltirish, chiroyli ko'rsatish kerak. Buni ilovada qatorma-qator qilish mumkin — yoki bitta SQL so'rov bilan butun jadvalda.

Birlashtirish: CONCAT

SELECT CONCAT(ism, ' (', shahar, ')') AS korinish FROM mijozlar;
-- Aziz Karimov (Toshkent)

Tuzoq: biror argument NULL bo'lsa — butun natija NULL:

SELECT CONCAT('Tel: ', telefon) FROM mijozlar WHERE id = 3;   -- NULL (Jasurning telefoni yo'q)

CONCAT_WS (with separator) — NULL larni tashlab ketadi:

SELECT CONCAT_WS(', ', ism, telefon, shahar) FROM mijozlar;
-- Aziz Karimov, +998901112233, Toshkent
-- Jasur Toshev, Toshkent                    ← NULL tashlab ketildi

Uzunlik: LENGTH va CHAR_LENGTH

SELECT LENGTH('Salom'), CHAR_LENGTH('Salom');       -- 5, 5
SELECT LENGTH('Салом'), CHAR_LENGTH('Салом');       -- 10, 5
SELECT LENGTH('👋'),    CHAR_LENGTH('👋');           -- 4, 1
  • LENGTH — baytlar soni
  • CHAR_LENGTH — belgilar soni

utf8mb4 da kirill harfi 2 bayt, emoji 4 bayt. "Ism 50 belgidan oshmasin" tekshiruvi uchun — doim CHAR_LENGTH.

Katta-kichik harf

SELECT UPPER('salom'), LOWER('SALOM');   -- SALOM, salom

MySQL da INITCAP (har so'zni bosh harf bilan) yo'q. Bitta so'z uchun:

SELECT CONCAT(UPPER(LEFT(ism, 1)), LOWER(SUBSTRING(ism, 2))) FROM mijozlar;

Bo'sh joylarni olib tashlash: TRIM

SELECT TRIM('   aziz   ');          -- 'aziz'
SELECT LTRIM('   aziz   ');         -- 'aziz   '
SELECT RTRIM('   aziz   ');         -- '   aziz'
SELECT TRIM(BOTH '-' FROM '--kod--');   -- 'kod'

Kesib olish: SUBSTRING, LEFT, RIGHT

SELECT SUBSTRING('Toshkent shahri', 1, 8);   -- 'Toshkent' — 1-belgidan 8 ta
SELECT SUBSTRING('Toshkent shahri', 10);     -- 'shahri' — 10-belgidan oxirigacha
SELECT LEFT('+998901112233', 4);             -- '+998'
SELECT RIGHT('+998901112233', 4);            -- '2233'

[!INFO] SQL da pozitsiya 1 dan boshlanadi (dasturlash tillaridagidek 0 dan emas).

SUBSTRING_INDEX — ajratgich bo'yicha

SELECT SUBSTRING_INDEX('aziz@mail.uz', '@', 1);    -- 'aziz' — birinchi @ gacha
SELECT SUBSTRING_INDEX('aziz@mail.uz', '@', -1);   -- 'mail.uz' — oxirgi @ dan keyin

Email domenlari statistikasi:

SELECT SUBSTRING_INDEX(email, '@', -1) AS domen, COUNT(*) AS soni
FROM mijozlar
GROUP BY domen
ORDER BY soni DESC;

Qidirish: LOCATE, INSTR

SELECT LOCATE('@', 'aziz@mail.uz');      -- 5 — pozitsiya
SELECT LOCATE('x', 'aziz@mail.uz');      -- 0 — topilmadi

Almashtirish: REPLACE

SELECT REPLACE('+998 90 111-22-33', ' ', '');   -- '+99890111-22-33'

Telefon raqamlarini tozalash — bir nechta REPLACE ichma-ich:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(telefon, ' ', ''), '-', ''), '(', ''), ')', '')
FROM mijozlar;

REGEXP_REPLACE (MySQL 8.0+)

Bitta muntazam ifoda bilan — "raqamdan boshqa hamma narsani olib tashla":

SELECT REGEXP_REPLACE('+998 (90) 111-22-33', '[^0-9]', '');   -- '998901112233'

Butun jadvalni tozalash

-- Avval ko'rib chiqish
SELECT telefon, REGEXP_REPLACE(telefon, '[^0-9]', '') AS toza FROM mijozlar;

-- Keyin yangilash
UPDATE mijozlar
SET telefon = CONCAT('+', REGEXP_REPLACE(telefon, '[^0-9]', ''))
WHERE telefon IS NOT NULL;

UPDATE mijozlar SET email = LOWER(TRIM(email));
UPDATE mijozlar SET ism = TRIM(ism);

To'ldirish: LPAD, RPAD

SELECT LPAD(42, 6, '0');          -- '000042'
SELECT CONCAT('B-', LPAD(id, 6, '0')) AS buyurtma_raqami FROM buyurtmalar;
-- B-000001, B-000002...

Shablon bo'yicha qidirish: REGEXP

LIKE dan kuchliroq:

-- O'zbekiston mobil raqami formatidagi telefonlar
SELECT telefon FROM mijozlar WHERE telefon REGEXP '^\\+998[0-9]{9}
#39;;

-- Noto'g'ri formatdagilarni topish
SELECT id, telefon FROM mijozlar
WHERE telefon IS NOT NULL AND telefon NOT REGEXP '^\\+998[0-9]{9}
#39;;

Boshqa foydali funksiyalar

SELECT REPEAT('-', 10);                  -- '----------'
SELECT REVERSE('salom');                 -- 'molas'
SELECT FORMAT(1250000, 0);               -- '1,250,000'
SELECT INSERT('998901112233', 6, 4, '****');   -- '99890****233' — maskalash
SELECT FIELD('b', 'a', 'b', 'c');        -- 2

Telefonni maskalab ko'rsatish (operatorlar uchun):

SELECT ism, CONCAT(LEFT(telefon, 6), '*****', RIGHT(telefon, 2)) AS telefon
FROM mijozlar WHERE telefon IS NOT NULL;
-- +99890*****33

Indeks va funksiyalar

-- ❌ Indeksdan foydalanib bo'lmaydi
SELECT * FROM mijozlar WHERE LOWER(email) = 'aziz@mail.uz';

-- ✅
SELECT * FROM mijozlar WHERE email = 'aziz@mail.uz';

Ustun funksiyaga o'ralganda MySQL indeksni ishlata olmaydi — har bir qator uchun funksiyani hisoblab, solishtiradi. Yechim: ma'lumotni yozishda tozalab saqlang (email doim kichik harfda), yoki case-insensitive collation ga tayaning (4-dars). MySQL 8 da ifoda bo'yicha indeks ham mumkin (31-dars).

Nima uchun shunday?

Nima uchun ma'lumotni yozishda tozalash kerak — o'qishda LOWER(TRIM(...)) qilsa ham bo'ladi-ku?

Chunki o'qish yozishdan yuzlab marta ko'p bo'ladi. Har bir o'qishda tozalash — har safar ortiqcha hisob va indeksdan foydalana olmaslik. Bundan tashqari, UNIQUE cheklov Aziz@Mail.UZ va aziz@mail.uz ni (binary collation da) har xil deb hisoblaydi — dublikat akkaunt paydo bo'ladi. Qoida: kirishda normallashtiring, keyin ma'lumot doim toza.

Xulosa

Eslab qoling:

  • CONCAT — NULL bo'lsa natija NULL; CONCAT_WS — NULL larni tashlaydi
  • CHAR_LENGTH — belgilar, LENGTH — baytlar
  • TRIM, UPPER, LOWER — tozalash
  • SUBSTRING(s, boshi, soni) — 1 dan; SUBSTRING_INDEX — ajratgich bo'yicha
  • REPLACE, REGEXP_REPLACE — almashtirish; REGEXP — format tekshiruvi
  • LPAD — raqamlarni nol bilan to'ldirish
  • WHERE da ustunni funksiyaga o'ramang — indeks ishlamaydi; yozishda normallashtiring

Keyingi dars

26-dars: Number funksiyalar — ROUND, CEIL, FLOOR, MOD, ABS