SELECT
  -- NAME: 
  LEFT(TRIM(REGEXP_REPLACE(
    TRANSLATE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(ad.name, ''', ''), '''', ''), 
            'Ä', 'Ae'), 'ä', 'ae'), 'Ö', 'Oe'), 'ö', 'oe'), 'Ü', 'Ue'), 'ü', 'ue'), 'ß', 'ss'),
            '½', ' 1/2'),         
            '–', '-'),           
            'u'||CHR(776), 'ue'),
            'a'||CHR(776), 'ae'),
            'o'||CHR(776), 'oe'),
        'ÀÁÂÃÅàáâãåÈÉÊËèéêëÌÍÎÏìíîïÒÓÔÕØóòôõøÙÚÛùúûÝýÑñÇç', 
        'AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUuuuYyNnCc'
    ),
    '[;' || CHR(10) || CHR(13) || CHR(9) || '"' || CHR(160) || ']+', 
    ' ')), 49) AS "Name",
    
  -- ORT
  LEFT(TRIM(REGEXP_REPLACE(
    TRANSLATE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(ad.city, ''', ''), '''', ''), 
            'Ä', 'Ae'), 'ä', 'ae'), 'Ö', 'Oe'), 'ö', 'oe'), 'Ü', 'Ue'), 'ü', 'ue'), 'ß', 'ss'),
            '½', ' 1/2'), '–', '-'), 'u'||CHR(776), 'ue'), 'a'||CHR(776), 'ae'), 'o'||CHR(776), 'oe'),
        'ÀÁÂÃÅàáâãåÈÉÊËèéêëÌÍÎÏìíîïÒÓÔÕØóòôõøÙÚÛùúûÝýÑñÇç', 
        'AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUuuuYyNnCc'
    ),
    '[;' || CHR(10) || CHR(13) || CHR(9) || '"' || CHR(160) || ']+', 
    ' ')), 30) AS "Ort",
    
  CASE 
      WHEN COALESCE(ad.supplier_number_accounting, '') = '' THEN ad.supplier_number 
      ELSE ad.supplier_number_accounting 
  END AS "Kontonummer",
    
  LEFT(TRIM(ad.supplier_number), 15) AS "Kundennummer",
  SUBSTRING(UPPER(REGEXP_REPLACE(ad.vat_number, '[^a-zA-Z0-9]', '')), 3) AS "EU-UStID",
  LEFT(TRIM(ad.bank_account), 30) AS "Bankkonto",
  LEFT(TRIM(ad.bank_code), 10) AS "BLZ",
   
  -- BANKNAME
  LEFT(TRIM(REGEXP_REPLACE(
    TRANSLATE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(ad.bank_name, ''', ''), '''', ''), 
            'Ä', 'Ae'), 'ä', 'ae'), 'Ö', 'Oe'), 'ö', 'oe'), 'Ü', 'Ue'), 'ü', 'ue'), 'ß', 'ss'),
            '½', ' 1/2'), '–', '-'), 'u'||CHR(776), 'ue'), 'a'||CHR(776), 'ae'), 'o'||CHR(776), 'oe'),
        'ÀÁÂÃÅàáâãåÈÉÊËèéêëÌÍÎÏìíîïÒÓÔÕØóòôõøÙÚÛùúûÝýÑñÇç', 
        'AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUuuuYyNnCc'
    ),
    '[;' || CHR(10) || CHR(13) || CHR(9) || '"' || CHR(160) || ']+', 
    ' ')), 29) AS "Bankname",
     
  LEFT(UPPER(REGEXP_REPLACE(ad.vat_number, '[^a-zA-Z0-9]', '')), 2) AS "Länderkennzeichen",
  LEFT(REPLACE(ad.iban, ' ', ''), 34) AS "IBAN",
  LEFT(TRIM(ad.bic), 11) AS "BIC",
  ad.customer_type AS "Anrede",
   
  -- ADRESSZUSATZ
  LEFT(TRIM(REGEXP_REPLACE(
    TRANSLATE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(ad.address_supplement, ''', ''), '''', ''), 
            'Ä', 'Ae'), 'ä', 'ae'), 'Ö', 'Oe'), 'ö', 'oe'), 'Ü', 'Ue'), 'ü', 'ue'), 'ß', 'ss'),
            '½', ' 1/2'), '–', '-'), 'u'||CHR(776), 'ue'), 'a'||CHR(776), 'ae'), 'o'||CHR(776), 'oe'),
        'ÀÁÂÃÅàáâãåÈÉÊËèéêëÌÍÎÏìíîïÒÓÔÕØóòôõøÙÚÛùúûÝýÑñÇç', 
        'AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUuuuYyNnCc'
    ),
    '[;' || CHR(10) || CHR(13) || CHR(9) || '"' || CHR(160) || ']+', 
    ' ')), 36) AS "Adresszusatz",
    
  -- STRASSE
  LEFT(TRIM(REGEXP_REPLACE(
    TRANSLATE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(ad.street, ''', ''), '''', ''), 
            'Ä', 'Ae'), 'ä', 'ae'), 'Ö', 'Oe'), 'ö', 'oe'), 'Ü', 'Ue'), 'ü', 'ue'), 'ß', 'ss'),
            '½', ' 1/2'), '–', '-'), 'u'||CHR(776), 'ue'), 'a'||CHR(776), 'ae'), 'o'||CHR(776), 'oe'),
        'ÀÁÂÃÅàáâãåÈÉÊËèéêëÌÍÎÏìíîïÒÓÔÕØóòôõøÙÚÛùúûÝýÑñÇç', 
        'AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUuuuYyNnCc'
    ),
    '[;\r\n\t"' || CHR(160) || ']+', 
    ' ')), 36) AS "Strasse",
    
  LEFT(TRIM(ad.zip_code), 10) AS "PLZ",
  LEFT(TRIM(ad.tax_number), 20) AS "Steuernummer",
   
  -- ANSPRECHPARTNER
  LEFT(TRIM(REGEXP_REPLACE(
    TRANSLATE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(ad.contact_person, ''', ''), '''', ''), 
            'Ä', 'Ae'), 'ä', 'ae'), 'Ö', 'Oe'), 'ö', 'oe'), 'Ü', 'Ue'), 'ü', 'ue'), 'ß', 'ss'),
            '½', ' 1/2'), '–', '-'), 'u'||CHR(776), 'ue'), 'a'||CHR(776), 'ae'), 'o'||CHR(776), 'oe'),
        'ÀÁÂÃÅàáâãåÈÉÊËèéêëÌÍÎÏìíîïÒÓÔÕØóòôõøÙÚÛùúûÝýÑñÇç', 
        'AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUuuuYyNnCc'
    ),
    '[;' || CHR(10) || CHR(13) || CHR(9) || '"' || CHR(160) || ']+', 
    ' ')), 40) AS "Ansprechpartner",
    
  LEFT(REGEXP_REPLACE(ad.phone_number, '[^0-9\+\-\/\(\) ]', ''), 60) AS "Telefonnummer",
  LEFT(TRIM(ad.email), 60) AS "E-Mail",
  LEFT(REGEXP_REPLACE(ad.fax, '[^0-9\+\-\/\(\) ]', ''), 60) AS "Faxnummer",

  -- INTERNETADRESSE
  LEFT(TRIM(REGEXP_REPLACE(
    TRANSLATE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(ad.website, ''', ''), '''', ''), 
            'Ä', 'Ae'), 'ä', 'ae'), 'Ö', 'Oe'), 'ö', 'oe'), 'Ü', 'Ue'), 'ü', 'ue'), 'ß', 'ss'),
            '½', ' 1/2'), '–', '-'), 'u'||CHR(776), 'ue'), 'a'||CHR(776), 'ae'), 'o'||CHR(776), 'oe'),
        'ÀÁÂÃÅàáâãåÈÉÊËèéêëÌÍÎÏìíîïÒÓÔÕØóòôõøÙÚÛùúûÝýÑñÇç', 
        'AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUuuuYyNnCc'
    ),
    '[;' || CHR(10) || CHR(13) || CHR(9) || '"' || CHR(160) || ']+', 
    ' ')), 60) AS "Internetadresse",

  ad.country AS "Land",
  LEFT(UPPER(REGEXP_REPLACE(ad.vat_number, '[^a-zA-Z0-9]', '')), 2) AS "Länderkennzeichen_2"

FROM addresses ad
INNER JOIN address_roles ar ON ad.address_id = ar.address_id

WHERE (ad.name IS NOT NULL AND ad.name <> '')
  AND ar.subject = 'Lieferant'

ORDER BY ad.logged_at DESC;