Oracle SQL字符串截取需求求助：处理ACENTE_AD与CEpTELEFON字段

Got it, let's tackle your two requirements step by step. I'll modify your existing query to meet both needs, plus a quick note on a potential logic issue in your WHERE clause that could cause unexpected results.

Modified Query #

WITH cep_telefon_data AS (
    SELECT 
        mr.musteri_rol_id,
        mi.aciklama AS cep_full
    FROM musteri_iletisim mi
    JOIN musteri_rol_iletisim mri 
        ON mi.musteri_iletisim_id = mri.musteri_iletisim_id
    WHERE mi.iletisim_tip_kod = pck_const_iletisim_tip.cep_telefon()
)
SELECT 
    -- Requirement 1: Show first 5 characters of ACENTE_AD
    SUBSTR(MR.MUSTERI_ROL_AD, 1, 5) AS ACENTE_AD,
    (SELECT mrrt.musteri_rol_ad 
     FROM calisan ct
     JOIN musteri_rol mrrt ON ct.calisan_rol_id = mrrt.musteri_rol_id
     JOIN musteri mmt ON mrrt.musteri_id = mmt.musteri_id
     WHERE ct.bagli_rol_id = a.acente_id 
       AND ct.teknik_personel='H' 
       AND ct.calisan_rol_id IS NOT NULL 
       AND rownum < 2) AS Bayi_Yetkili_Kisi,
    CASE WHEN M.TCK_NO IS NULL THEN M.VKN_NO ELSE NULL END AS VKN_NO,
    (SELECT taa.adresi 
     FROM tobb_acente ta
     JOIN tobb_acente_adres taa ON ta.tobb_acente_sorgu_id = taa.tobb_acente_sorgu_id
     WHERE ta.levhano = a.levha_no 
       AND rownum < 2) AS adress,
    -- Requirement 2: Split CEpTELEFON into two columns
    SUBSTR(ctd.cep_full, 1, 3) AS CEpTELEFON_ILK_UCHAR,
    SUBSTR(ctd.cep_full, 4, 6) AS CEpTELEFON_SON_ALTI
FROM MUSTERI_ROL MR
JOIN MUSTERI M ON MR.MUSTERI_ID = M.MUSTERI_ID
JOIN ACENTE A ON MR.MUSTERI_ROL_ID = A.ACENTE_ID
JOIN SATIS_KANALI SK ON A.SATIS_KANALI_ID = SK.SATIS_KANALI_ID
LEFT JOIN cep_telefon_data ctd ON MR.MUSTERI_ROL_ID = ctd.musteri_rol_id
WHERE MR.ROL_ID = 1 
  AND A.Uretim_Kaynagi = 'E' 
  AND A.UST_ACENTE_ID IS NULL 
  AND MR.Ust_Musteri_Rol_Id IS NULL 
  AND M.VKN_NO != 'x' 
  -- Fixed logic priority: AND takes precedence over OR, so wrap date conditions
  AND (TO_CHAR(TRUNC(MR.Bitis_Tarih), 'DD/MM/YYYY') IS NULL OR MR.Bitis_Tarih = SYSDATE);

Key Explanations #

• ACENTE_AD Truncation: I used Oracle's SUBSTR function (SUBSTR(MR.MUSTERI_ROL_AD, 1, 5)). The parameters are: the source string, starting position (Oracle uses 1-based indexing), and number of characters to extract. If the original string is shorter than 5 characters, it will return the entire string without errors.

• CEpTELEFON Splitting:

  • I moved the original CEpTELEFON subquery into a CTE (cep_telefon_data) to avoid repeating the same logic twice—this makes the code cleaner and more efficient.
  • Then, SUBSTR splits the full phone number:
    • SUBSTR(ctd.cep_full, 1, 3) grabs the first 3 characters (area code)
    • SUBSTR(ctd.cep_full, 4, 6) grabs the next 6 characters (main number)
  • A LEFT JOIN ensures we don't lose rows where there's no phone number (those columns will just show NULL).

• Logic Fix Note: Your original WHERE clause had an unparenthesized OR condition. Since AND has higher precedence than OR, this would have mixed your date filters with other conditions incorrectly. Wrapping the date checks in parentheses enforces the intended logic.

内容的提问来源于stack exchange，提问作者Joe