Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: TRANSLATE Jump to:  
Category: >> Oracle PL/SQL >> TRANSLATE Bookmark and Share

<< lastnext >>

Snippet Name: TRANSLATE

Description: Converts a character string into the character set specified for conversions between the database character set and the national character set.

Note: The TRANSLATE ... USING function is supported primarily for ANSI compatibility. Oracle recommends that you use the TO_CHAR and TO_NCHAR functions, as appropriate, for converting data to the database or national character set. TO_CHAR and TO_NCHAR can take as arguments a greater variety of datatypes than TRANSLATE ... USING, which accepts only character data.

The char_string argument is the expression to be converted.

Specifying the USING CHAR_CS argument converts char into the database character set. The output datatype is VARCHAR2.

Specifying the USING NCHAR_CS argument converts char into the national character set. The output datatype is NVARCHAR2.

This function is similar to the Oracle CONVERT function, but must be used instead of CONVERT if either the input or the output datatype is being used as NCHAR or NVARCHAR2. If the input contains UCS2 code points or backslash characters (\), then use the UNISTR function.

Also see:
» Add PSOUG Search to SQL Developer
» Converting Rows to Columns
» UNISTR
» TO_YMINTERVAL
» TO_TIMESTAMP_TZ
» TO_TIMESTAMP
» TO_SINGLE_BYTE
» TO_NUMBER
» TO_NCLOB
» TO_NCHAR
» TO_MULTI_BYTE
» TO_LOB
» TO_DSINTERVAL
» TO_DATE
» TO_CLOB
» TO_CHAR
» TO_BINARY_FLOAT
» TO_BINARY_DOUBLE
» TIMESTAMP_TO_SCN
» SCN_TO_TIMESTAMP
» ROWIDTONCHAR
» ROWIDTOCHAR
» REFTOHEX
» RAWTONHEX
» RAWTOHEX
» NUMTOYMINTERVAL
» NUMTODSINTERVAL
» HEXTORAW
» DECOMPOSE
» CONVERT

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 05th, 2009

TRANSLATE(char_string USING <CHAR_CS character_set | NCHAR_CS character set>)
 
 
CREATE TABLE translate_tab (char_col  VARCHAR2(100),
                            nchar_col NVARCHAR2(50));
INSERT INTO translate_tab 
   SELECT NULL, translated_name
      FROM product_descriptions
      WHERE product_id = 3501;
 
SELECT * FROM translate_tab;
 
CHAR_COL                  NCHAR_COL
------------------------- -------------------------
. . .
                          C per a SPNIX4.0 - Sys
                          C pro SPNIX4.0 - Sys
                          C FOR SPNIX4.0 - Sys
                          C til SPNIX4.0 - Sys
. . .
 
UPDATE translate_tab 
   SET char_col = TRANSLATE (nchar_col USING CHAR_CS);
 
SELECT * FROM translate_tab;
 
CHAR_COL                  NCHAR_COL
------------------------- -------------------------
. . .
C per a SPNIX4.0 - Sys    C per a SPNIX4.0 - Sys
C pro SPNIX4.0 - Sys      C pro SPNIX4.0 - Sys
C FOR SPNIX4.0 - Sys      C FOR SPNIX4.0 - Sys
C til SPNIX4.0 - Sys      C til SPNIX4.0 - Sys
. . .
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 158 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?