PL/SQL :: RETURN Translate - Greek Letters Replace By Their Corresponding Values
Sep 20, 2012
Imagine I have the following function:
FUNCTION normalize(str IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN TRANSLATE(LOWER(str),
'äàáâãăāåąæčçðďéèëêěĕėęğģġîĭïīìíłļľŀñńňņöóòôõσøőřśŝšşţüúùûǔųūůŵýÿżźžżαβßγδεζŋηικλμµνξπρσςτυφω',
'aaaaaaaaaaccddeeeeeeeegggiiiiiillllnnnnoooooooorsssstuuuuuuuuwyyzzzzassydeznniklmmnxprsstufo'
);
END;
I'm tired to add missing characters in this list...
What I would like is that all characters with an accent or diacritics should be replace by their "base" letter (ë -> e) and Greek letters to be replace by their corresponding values (ω (omega) -> o)
View 1 Replies
ADVERTISEMENT
Jan 6, 2013
Explain with the best example for difference between translate and replace function.
View 2 Replies
View Related
Nov 3, 2010
I have requirement to cleanup special character of field. The data which is retrieved from the field and written to text file.
So Here
Ã- should be replaced by i
HÃ-re - I need value Hire(actual value) from the filed.
Ã- this has written into file instead of i
In database i have the following
NLS_NCHAR_CHARACTERSET - AL16UTF16
NLS_CHARACTERSET - UTF8
I tried this
select convert('HÃ-re','UTF8') from dual
------------------------
Output: H??re
View 6 Replies
View Related
Jul 28, 2010
I have a table abc with two column (marks,id) both can have any value.
Value of the id column is zero at several places.When I divide marks by id. I get divide by zero error.
how to replace zero with null.
View 22 Replies
View Related
Jun 26, 2012
I have one emp table, in which i have sal, ename,empno etc columns. Now i am trying to replace each digit of salary with '*'. like if salary is 10000(it has five digit) then it will be replace by *****, but unable to do so. I don't how to achieve this, can we use translate or replace function in this.
Well i have achieved this by using case statement but where hardcoding is done...see the case below:-
select substr(ename,1,8),case when length(sal)=2
then '**'
when length(sal)=3
then '***'
when length(sal) = 4
then'****'
when length(sal)=5 then
[code]....
View 27 Replies
View Related
Jun 17, 2010
I have a large table with a few billion rows. I need to find ITEM_NAME values in ITEM_MASTER table containing " " (spaces) and replace them with other values. This has to be done within the same script. The replacement values could be any characters or numbers, but not spaces.
The whole reason for this table is testing big databases, so it does not matter what it contains. The reason I need to replace them is that this table will be dumped to a space delimited flat file and spaces within values are not acceptable.
View 7 Replies
View Related
Nov 8, 2012
I have two tables A and B.
A
--
variable value
-------- ------
a 10
b 20
B
--
Exp
---
b-a
b*a
b/a
How can the variables be replaced with values(10,20) using a single query...?
View 2 Replies
View Related
Jan 23, 2013
I am trying to insert the following greek character in the database. DB is not supporting the character and saved as junk character in the DB. create table MULTI_LANG (id number, comments NVarchar2(200)); insert into MULTI_LANG values (1,'Οι παραπάνω υπολογισμο ');
DB version Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
LANGUAGE AMERICAN
TERRITORY AMERICA
CHARACTER SET US7ASCII
NLS_CHARACTERSET US7ASCII
NLS_NCHAR_CHARACTERSETAL16UTF16
I have the same problem with the following db version also:
1) LANGUAGE AMERICAN
TERRITORY AMERICA
CHARACTER SET WE8MSWIN1252
2) NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16
3) Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
View 4 Replies
View Related
Apr 28, 2011
I want to search a some values in oracle table and then return the corresponding column names.
View 1 Replies
View Related
Aug 2, 2010
whether a function can return two values?
View 4 Replies
View Related
Aug 26, 2010
I want to return 100 values by using the procedure. In that we have to declare 100 OUT parameters. So it's a time consuming process.
Instead of declaring 100 OUT parameters. How can we return 100 values in a single shot.
View 1 Replies
View Related
Dec 19, 2012
the following procedure successfully compiled and executed. How to access the values returning from this procedure in another procedure or PLSQL block.
CREATE OR REPLACE PROCEDURE test IS
cursor c1 is SELECT a.idnumber, a.idp FROM holdings a;
r1 c1%rowtype;
BEGIN
open c1;
loop
fetch c1 into r1;
exit when c1%notfound;
DBMS_OUTPUT.put_line (r1.idnumber||'--'||r1.idp);
end loop;
close c1;
END;
View 14 Replies
View Related
Aug 28, 2010
I want multiple values from a function. I want to use this function in a SQL query. Here i'm giving my try.
SQL> CREATE TABLE TEMP
2 (
3 ID NUMBER(1),
4 SAMPTYPE VARCHAR2(20 BYTE),
5 SALARY NUMBER(10)
6 )
7 /
Table created.
SQL> INSERT INTO TEMP VALUES(1,'ABC',10000);
1 row created.
SQL> INSERT INTO TEMP VALUES(2,'PQR',20000);
1 row created.
SQL> INSERT INTO TEMP VALUES(3,'JPD',5000);
1 row created.
SQL> COMMIT;
Commit complete.
[code]...
Here i get result as ABC*10000, but i want two separate values as ABC,10000. how can i do this via function.
View 6 Replies
View Related
Dec 16, 2009
I am trying write a script that will return all values (based on the minimum tarif) from the Germany table for any duplicate values. Duplicate values are any values with the same UFI, ZC,limitid,depot. The German table also contains the fields tarif, city, supplier, etc.
Below is the script I have previously used to sort out duplicates. I have tried 50 different ways get it to return just lines for the minimum tariff but haven't been successful.
select *
from Germany t
where (ufi,zc,limitid,depot) in (
select ufi,zc,limitid,depot from (
select ufi,zc,limitid,depot, count(*) n
from Germany t
group by ufi,zc,limitid,depot)
where n<>1
)
View 4 Replies
View Related
Nov 29, 2012
I have a stored procedure which has varchar2 as IN and sys_recursor has OUT parameters.
CREATE OR REPLACE PROCEDURE check_values (
my_values IN emp.dept_no%TYPE,
p_cursor OUT sys_refcursor
)
[Code]....
/The problem I am facing is in where condition, if I give quoteValues it doesn't fetch me any records when I execute the procedure from sqlplus, but if I am giving my_values it does fetch me records. I am receiving IN parameters like 9856,9712,8723, so first I put single quote around the emp_no and pass that to where condition.
How can I resolve this issue?
View 15 Replies
View Related
Mar 13, 2012
I have a table second_table which has a username and code field. A username (not the primary key) may be entered multiple times with different codes, or a single code 'ALL', in which case, the codes have to be fetched from 'third_table'. I am unable to form a 'case' clause to handle the fact that a list has to returned when the 'code' field from the second_table returns 'ALL'.
e.g.
Second_table
username code
A ALL
B 23
B 56
B 33
Third_Table
code
67
78
So, when the user asks the codes for user A, he should get 67 and 78 and when he asks for the user B, he should get 23,56 and 33
View 13 Replies
View Related
Apr 8, 2013
I am using functions to return multiple values of two rows or multiple rows.
For example emp id = 100 and i need to return the value for this(empid) input and output of this first_name and salary.
I am tried in this way below but got errors (ORA-00932: inconsistent datatypes: expected NUMBER got HR.EMP_TYPE)
create or replace type emp_type as object(first_name varchar2(20),salary number);
create or replace function f1(empid in number)
return emp_type
as
emp_record emp_type;
begin
select first_name,salary into emp_record.first_name,emp_record.salary from employees where employee_id = empid ;
return emp_record;
end;
/
select f1(100) from dual;
View 12 Replies
View Related
Jul 3, 2013
I have a PL/SQL function where i call java class. In java i`m making some calculations and i need to return to body of PL/SQL function some values ( like 6 to 10, few numbers, string, 1 xml and 1 html).
I will show pl/sql function..
CREATE OR REPLACE FUNCTION GET_TAB(P_CURS SYS_REFCURSOR) RETURN TYP1_TAB PIPELINED IS
OUT_REC TYP1:=TYP1(NULL,NULL,NULL,NULL);
V_NAZWA VARCHAR2(5);
V_NUMER NUMBER;
[code].....
View 26 Replies
View Related
Dec 22, 2011
I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.
I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.
WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......
DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes
The results I am expecting is
DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14
I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.
SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN
But I don't think my manager will allow me to deploy a Multi Row function .
View 2 Replies
View Related
Feb 26, 2013
I have the following database function.
GetRegionDetails(id in varchar2, o_lat out number, o_lon out number);
The problem is, the output values are returning as whole numbers ie. 38.108766567 is being returned as 38 and -78.16423574566 is returned as 78
what data type I should use so that my output is returns all the decimal values?
View 5 Replies
View Related
Feb 15, 2012
Well i like to display the Values in Boiler Plate/Text. For Example, i have Report with Address Columns. for Tin No: i have Hard coded as TIN: 34XXXXXX01 and i like to change this hard coded item as TIN: 34XXXXXX02 so during Runtime if the input is changed the above item to be changed.
Inputs given in Report Parameter;
if the input is given as 81
TIN: 34XXXXXX01
if the input is given other than 81
it should print as TIN: 34XXXXXX02
How to give it in the Boiler plate.?
View 6 Replies
View Related
Nov 22, 2012
resolve issue while modified the user function code for returns the values as timestamps
---function code
create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin
[Code].....
Input:-
select fun_test_timestamp('5') from dual;
Output:-
SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;
Modified Fun Code:
create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);
[Code]...
Input:-
select fun_test_timestamp('5') from dual;
Output:-
ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
*Action:
View 6 Replies
View Related
Dec 18, 2012
I'm having a report deriving data from the database, words are written properly in the database, but in my report some words have space between the letters
View 1 Replies
View Related
Jul 4, 2013
I have 11.2.0.3 Db in HP UX 11.31, I have to spool a table output in csv format which contains arabic letters in few columns. my spool output is not showing the arabic letters properly.
[oratest@]$ echo $NLS_LANGAmerican_America.
AL32UTF8 V$NLS_PARAMETERS NLS_LANGUAGE
AMERICANNLS_TERRITORY
AMERICANLS_CURRENCY
$NLS_ISO_CURRENCY
AMERICANLS_NUMERIC_CHARACTERS
.,NLS_CALENDAR
[Code]....
View 0 Replies
View Related
Aug 16, 2011
How to Check for party names contains standalone four length keywords of consecutive letters(both upper and lower case)
For.e.g. "VMWARE ABCD"
"Jack XWyz jon"
output shoud be: abcd
ABCD,
XWYZ,
like this i want to remove the consecutive characters.
View 39 Replies
View Related
Oct 24, 2012
I've edited the XML on the Chart Attributes page but I don't find atrtributes to rotate the y-axis title (<text>TITLE</text>) nor the letters themselves in the title. What I'd like is something very close to
T
I
T
L
E
But what I get is a horizontal title rotated -90 degrees. I've looked at lots of Anychart documentation but don't find anything that seems to fit with the XML already given on the Chart Attributes page. I've seen the "rotate" attribute but I don't know where it would go and even then, I'm sure it only rotates the title as a unit and not the individual letters.
View 0 Replies
View Related
Jun 30, 2012
I have two tables,
CREATE TABLE TBL_ID(
SERIAL NUMBER(3),
ID NUMBER(5));
CREATE TABLE TBL_ID2(
ID NUMBER(5));
[code]...
I want translate id from TBL_ID2 to TBL_ID, and make new serial as max serial + 1 I tried
insert into TBL_ID(serial,id)
select (select max(serial) + 1 from ),id from TBL_ID2
Desired Output after insert
select * from TBL_ID
SERIAL ID
------ --
1 10
2 20
3 50
4 60
5 90
View 3 Replies
View Related
Jul 16, 2010
I have 70 materialized views where I need to replace the FROM SCHEMA1 TO FROM SCHEMA2...To quickly to do the fix for all the 70 views..
SELECT REPLACE (QUERY, 'schema1', 'schema2' )FROM USER_MVIEWS ;
But It throws me an error that Number cannot be replace.
View 14 Replies
View Related
Apr 22, 2013
I'm trying to translate all of the interactive report messages, on Apex 4.2. I'm using this [
[URL].........
As a reference. I have several problems translating the help section of the interactive report. First of all, I can't seem to find all of the message names. The ones I couldn't find were:
Sets the number of records to display per page.(regarding displaying certain number of rows per page)
Format enable you to customize the display of the report. Format contains the following sub menu:
Sort
Control Break
Highlight
Compute
Aggregate
Chart
Group By(regarding format)
You can define one Group By report per saved report. Once defined, you can switch between the group by and report views using links below the Search bar. To create a Group By report, you select:
the columns on which to group (up to 3 columns can be selected)
the columns to aggregate along with the function to be performed (average, sum, count, etc.)
the columns to use for sorting (up to 3 columns can be selected along with direction and null sorting options)(regarding group by)
And secondly, I have translated this message
Select columns icon enables you to identify which column to search (or all).
Text area enables you to enter case insensitive search criteria (wild card characters are implied).
Go button executes the search.
Reports displays alternate default and saved private or public reports.
Actions Menu enables you to customize a report. See the sections that follow.using this message name:
APEXIR_HELP_SEARCH_BAR.
However, now I have two messages shown in that section: the original one, in English, and the translated one.
View 1 Replies
View Related
Feb 7, 2013
I am on 11g.
I need to remove the alpha characters from a string, leaving only numbers, but I am getting unexpected results:
SQL> SELECT TRANSLATE('3N', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', NULL) a FROM DUAL;
A
-
I thought this would leave the 3 from the 3N, but it is returning an empty string. For my application, the string '3N' could be any length, will only contain letters and numbers, and the letters will always come at the end, but there could be more than one letter
VALID INPUT samples:
4
25
11F
361NG
8ABC
View 6 Replies
View Related
