ap2i/sql/schema-extensions/003-view_organization_identification.sql

CREATE OR REPLACE VIEW view_organization_identification AS
    SELECT o.id, o.name, o.identifier, o.siretNumber, o.waldecNumber,
           a.streetAddress, a.streetAddressSecond, a.streetAddressThird, a.addressCity, a.postalCode,
           c.email, c.telphone,
           REGEXP_REPLACE(
                   REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(LOWER(trim(o.name)), '[éèê]', 'e'), '[à]', 'a'), '[ç]', 'c'),
                   '[^a-z0-9]+',
                   '+'
           ) as normalizedName,
           REGEXP_REPLACE(
                   LOWER(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(trim(concat(a.streetAddress, ' ', a.streetAddressSecond, ' ', a.streetAddressThird)), '[éèê]', 'e'), '[à]', 'a'), '[ç]', 'c')),
                   '[^a-z0-9]+',
                   '+'
           ) as normalizedAddress
    FROM opentalent.Organization o
             INNER JOIN opentalent.OrganizationAddressPostal oa ON oa.organization_id = o.id
             INNER JOIN opentalent.AddressPostal a ON a.id = oa.addressPostal_id
             INNER JOIN opentalent.organization_contactpoint oc ON oc.organization_id = o.id
             INNER JOIN opentalent.ContactPoint c ON oc.contactPoint_id = c.id
    WHERE oa.type='ADDRESS_HEAD_OFFICE' AND c.contactType='PRINCIPAL';