Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Convert non-English characters to their English equivalent

GJoobJan 14 2016 — edited Jan 14 2016

Hi,

I am trying to write a procedure that generates email addresses from people's names.

There are a number of people with non-English characters in their names such as umlauts, fadas, graves etc. and I am wondering if there is a handy way to convert these to their English equivalent (e.g. convert 'á' to 'a' or 'ß' to 'ss')?

I tried using UTL_RAW.CAST_TO_VARCHAR2(NLSSORT(:the_name, 'nls_sort=binary_ai')) which seemed to work fine except that it also converts string to lower case.

Any suggestions would be much appreciated.

G'Joob

Comments

fionanycheng
Please use below SQL statement. It works well to retrieve Sales Order's Ship To Location.

-- SHIP TO NAME and SHIP TO DETAILS
select rsua.location ship_to_location,
raa.ADDRESS1 Ship_To_address1,
raa.ADDRESS2 Ship_to_Address2,
raa.address3 Ship_to_address3,
raa.country
from ra_site_uses_all rsua,
ra_addresses_all raa,
oe_order_headerS_all oha,
wsh_delivery_details wdd
where wdd.ship_to_site_use_id = rsua.SITE_USE_ID and
oha.order_number = '3022004731' and -- Sales Order Number
oha.header_id = wdd.source_header_id and
raa.address_id = rsua.address_id

It then work FINE now!
fionanycheng
Please use below SQL statement. It works well to
retrieve Sales Order's Ship To Location.

-- SHIP TO NAME and SHIP TO DETAILS
select rsua.location ship_to_location,
raa.ADDRESS1 Ship_To_address1,
raa.ADDRESS2 Ship_to_Address2,
raa.address3 Ship_to_address3,
raa.country
from ra_site_uses_all rsua,
ra_addresses_all raa,
oe_order_headerS_all oha,
where oha.ship_to_org_id = rsua.SITE_USE_ID and
oha.order_number = '3022004731' and --Sales Order Number
and raa.address_id = rsua.address_id

It then work FINE now!
No need to use WSH_DELIVERY_DETAILS table to retrieve the Ship To Locations. Otherwise, some sales order cannot show the "Ship To Location" in the report, "<<Company Abbr.>> Order Notice Printout".
Just_UD
Hi fiona and all, it happened that I need to retrieve the information same as you did with a little modification.

I can retrieve the customer name from the number in ship to and bill to fields if it was referred to the same customer but I failed if it was different customer, e.g. the 1st customer who bought an item wants it to delivered to 2nd customer, so that ship to field will be filled with 2nd customer's number, (which already had relationship with the 1st customer) but the invoice must go to 1st customer, so that the bill to field will be filled with 1st customer's number.

I already tried your script but it still gave me only the number, not the customer's name (we can see it in the shipping menu but I still couldn't retrieve it).

I already read the script (OE_ORDER_HEADERS_ALL) and I found that ship_to_location (for ship to field) and invoice_to_location (for bill to field) referred to same column (LOCATION) of the same table (HZ_CUST_ACCT_SITE_USES_ALL if I remembered it right) but I still haven't found out how can they gave different number. I already got the party_field, site_id field, etc and tried to split the queries but still failed.

Can anyone help? Thanks.
Just_UD
Hi all, I managed to solve my problem. Here's the query in case someone need it. Thanks

SELECT party.party_name "Send to" (SELECT party.party_name
FROM hz_cust_site_uses_all bill_su,
hz_cust_acct_sites_all bill_cas,
hz_party_sites bill_ps,
hz_locations bill_loc,
hz_parties party,
oe_order_headers_all h
WHERE h.invoice_to_org_id = bill_su.site_use_id
AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id
AND bill_cas.party_site_id = bill_ps.party_site_id
AND bill_loc.location_id = bill_ps.location_id
AND bill_cas.party_site_id = bill_ps.party_site_id
AND bill_ps.party_id = party.party_id
AND h.order_number = 'your_order_number') "Invoice to"
FROM oe_order_headers_all h,
hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all ship_cas,
hz_party_sites ship_ps,
hz_locations ship_loc,
hz_parties party
WHERE h.ship_to_org_id = ship_su.site_use_id
AND ship_cas.party_site_id = ship_ps.party_site_id
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id
AND ship_cas.party_site_id = ship_ps.party_site_id
AND ship_ps.party_id = party.party_id
AND ship_loc.location_id = ship_ps.location_id
AND h.order_number = 'your_order_number';
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 11 2016
Added on Jan 14 2016
8 comments
7,185 views