SQL & PL/SQL :: Numbers To Alphabets
Jul 6, 2013
I have a requirement to display numbers as equivalent alphabets , like if the stored value is 1 then it should display as 'A' ,2 means 'B' ,is there a way to find out.
CREATE TABLE APS ( ITEM_NO NUMBER, ITEM_NAME VARCHAR2(12))
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (1,'TEST1');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (2,'TEST2');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (3,'TEST3');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (4,'TEST4');
-- The output to be is.
item_no , item_name
A TEST1
B TEST2
C TEST3
D TEST4
View 23 Replies
ADVERTISEMENT
Apr 20, 2013
I need to find out that assume i have a table having 2 column
Num Name
1 Adam
2 Akanksha
1 barren
2 bosli
3 Benergee
4 Bhawna
3 anjani
I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4 and again there is anjani so 3 should be there as 1 and 2 in first 2 places and the num should be automatically generated number based on the count of the alphabets
View 6 Replies
View Related
Jul 13, 2011
I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :
TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209
My expected results should look like :
ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209
View 7 Replies
View Related
Oct 12, 2007
I have the following set of numbers that i am passing in as one input into a stored procedure.
234,456,234,456,567
Now i want to take this list of numbers and use it in an IN statement:
select * from table where column_a in (P_INPUT);
however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!
View 6 Replies
View Related
Jul 1, 2010
I want to get 10 random numbers from existing 100 numbers. How can we get/generate random numbers ?
for example I have a table with customer ID, customer Name, having 100 record. We want 10 customers ID randomly from that 100 record not repeated any number. Have any command or procedure for that ?
View 3 Replies
View Related
Jul 25, 2010
I have a column of datatype varchar2
this column may contain char or may contain number or may contain both
some values in this column are only number is there any function or any other way to get those column that are only number
View 3 Replies
View Related
Nov 1, 2010
Earlier, we had a discussion how to generate the permutations. generating the code for combinations
For eg: if I have input as [1,2,3,4,5] then 5c2 = 20 combinations should be generated as pairs
1,2
1,3
1,4
1,5
2,3
2,4
2,5
3,4
3,5
4,5
Total of 10 combinations
View 8 Replies
View Related
Apr 13, 2013
I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?
Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP
CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER)
RETURN NUMBER
IS
Sumnums NUMBER;
BEGIN
SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL;
DBMS_OUTPUT.PUT_LINE(Sumnums);
RETURN 1;
END sumfivenumbers;
/
SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;
View 7 Replies
View Related
Jul 6, 2012
I need to find the top two values value for each ID Number:
CREATE TABLE TABLE_1
(ID number (8),
NUMBER_1 number (2),
NUMBER_2 number (2),
NUMBER_3 number (2),
NUMBER_4 number (2));
INSERT INTO TABLE_1
VALUES
('12345679','30','25','30','05');
INSERT INTO TABLE_1
VALUES
('99999999','30','25','15','05');
Desired Result:
ID Number 1st 2nd
12345679 30 30
99999999 30 25
View 7 Replies
View Related
Jul 6, 2012
I tried to convert numbers to words, it shows the below error.
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 11:00:29 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;
TO_CHAR(TO_DATE(2447834,'J'),'JSP')
---------------------------------------------------------------------------
two million four hundred forty-seven thousand eight hundred thirty-four
SQL> SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL;
SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL
*
ERROR at line 1: ORA-01830: date format picture ends before converting entire input string
SQL>
View 4 Replies
View Related
Sep 18, 2012
Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;
but mean i have to add . after numbers . as follow
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;
but this is not right
i want to extract numbers without DOT also.
View 6 Replies
View Related
Mar 1, 2012
How to print even numbers 1 to 10.without using the MOD Function
View 17 Replies
View Related
May 12, 2010
I am trying to run this script:
Update ACT.CUSTOMER_CLIENT_REFERENCE
Set ORIGINAL_SOURCE_FG = 'N',
CANADIAN_ULTIMATE_REFERENCE_FG = 'N',
LUDT = SYSDATE,
CLT_NO = (Select clt_no from client.client where clt_no between 701885 and 705287)
Where Exists (Select 1 from client.client where clt_ofc_no = 19 and clt_no between 701885 and 705287)
But the CLT_NO = .... line is giving me this error: ORA-01427: single-row subquery returns more than one row
what i am trying to do is insert all the client numbers between 701885 and 705287 into ACT.CUSTOMER_CLIENT_REFERENCE
View 6 Replies
View Related
May 11, 2012
I have a table with three columns: terminal, place and batch. How can I check for missing batches?
select * from transactions;
terminal place batch
84812
84813
84814
84816
84821
84823
84824
84825
View 5 Replies
View Related
Mar 14, 2002
I found a message which explain how to spell out numbers to words. I'm french and the purpose of my question is how to convert numbers to word (to print cheque) but in french the function found in the newsgroup was :
select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
/
I'm afraid but i don't undaunted how pass my number to this function (&num ???)
View 12 Replies
View Related
Feb 16, 2010
create table t (
number_from number,
number_to number);
insert into t values(1,3);
insert into t values(5,9);
insert into t values(10,15);
commit;
I need to create a stored function that could find/return missing numbers between number_from and number_to for each record in table t.
For example:
Number_fromNumber_to missing
13
594 from previous record, this record is supposed to start from 4
View 24 Replies
View Related
Feb 5, 2011
I am developing a form where I need to add Numbers.In fact we have a bag of Cones that contain 24 cones.In normal calculation when I add numbers for example
5.24 Plus 5.24 it will give the result 10.48
I Need the appropriate method to calculate if I add these two numbers it should give the result 5.24 Plus 5.24 the result should be 12
View 7 Replies
View Related
Sep 21, 2011
I have a table A with 10000 records with values as 1 to 10000. Table B has columns as range,number1,number2. Totally there are three rows as
ROW1=range1,1,100 & ROW2=range2,300,500 & ROW3=range3,800,900
I need to select all records from table A when it is between any one of the three rows from table B.
View 13 Replies
View Related
Apr 10, 2012
acheive the below result.
Select * from result;
res
===
3
3
3
3
3
0
3
3
3
0
3
0
output:
I would like to get res1 using SQL query.
resres1
31
32
33
34
35
00
31
32
00
31
32
33
34
00
.. ....
View 9 Replies
View Related
Aug 5, 2011
how can show this query in this picture
select round(15.555,2) from dual appear final result 15.55 only
and this query don't be this result 15.56
because i have filed and always Decimal numbers be (15.555,2)
i want only result appear after two numbers and no rounded the numbers
View 2 Replies
View Related
Jan 30, 2013
Using DB 10.2.0.5, I've encountered a strange behavior today while trying to compare ORA_ROWSCN with a previous SCN stored in a column.
SELECT
h.id HID,
h.ora_rowscn HSCN,
o.id OID,
o.scn OSCN,
[code]......
1157 rows While casting o.scn to a number gives me another resultset (this one is correct):
SELECT
h.id HID,
h.ora_rowscn HSCN,
o.id OID,
o.scn OSCN,
h.ora_rowscn-o.scn DIFF
[code]......
2114 rows I got the same result if I use NVL(o.scn,0) rather than TO_NUMBER(o.scn). I can't find out why this happens. Obviously, the ">" condition doesn't match when the difference is too small.
AFAIK, ora_rowscn is a NUMBER while my "scn" column is a NUMBER(12) (which should be sufficient to store my DB or remote DB's SCN).
View 9 Replies
View Related
Oct 16, 2011
I have a table named tbl_student which contains 2226 rows have a column student_code of datatype number which contains student number in serial order from 1 to 2226.
I want to know what numbers between 1 to 2226 are missing.Is there a way in SQL by which I can query the above table for the desired result. There were 2246 records in all which means 20 unknown numbers (between 1 and 2226) are missing. No records have been deleted.
View 9 Replies
View Related
Oct 25, 2010
I have to write a stored procedure/function which has to generate the combination of numbers
For eg: IF I/p is an array of numbers a(i) = [1,2,3]
I want to get various combinations of numbers with these three digits.
writing the stored procedure generating the output for this
View 18 Replies
View Related
Oct 4, 2010
MyTable has missing records:
id_group id_column
-------------------------
1 1
1 2
1 3
1 5
1 9
2 1
2 2
2 4
2 6
As you see, it could be missed record 4,6,7 and 8 of group 1 and record 3 and 5 of group 2. That's is an example.
I need to show all sequential records, incluing missing ones, like this:
Id_group Id_column
-------------------------------------------------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
2 1
2 2
2 3
2 4
2 5
2 6
I try this query:
/*---*/
Select G.id_group, C.rownum id_column
from (select distinct id_group from MyTable) G
cross join (SELECT rownum FROM dual CONNECT BY LEVEL <= (select
max(id_column) from MyTable)) C
/*---*/
but it shows record 7, 8 and 9 as members of group 2.
View 9 Replies
View Related
Feb 28, 2013
I need to subtract multiple numbers to get the difference between each numbers, the amount of numbers to subtract between each others can vary between 2 and 10.
create table table_1 (
col1 varchar2(5),
col2 number(6,0),
col3 number(12,0)
)
insert into table_1 values ('AAA', 34379, 11111);
insert into table_1 values ('AAA', 39032, 11111);
insert into table_1 values ('AAA', 54337, 11111);
insert into table_1 values ('AAA', 78005, 11111);
insert into table_1 values ('AAA', 66793, 22222);
insert into table_1 values ('AAA', 74323, 22222);
insert into table_1 values ('AAA', 81426, 22222);
Expected Output:
col1 col2 col3
AAA -4653 11111
AAA -15305 11111
AAA -23668 11111
AAA -7530 22222
AAA -7103 22222
View 6 Replies
View Related
Jan 10, 2011
Need to write a function that should allow only characters and numbers in Login ID using pl sql.
View 7 Replies
View Related
Jun 15, 2010
I have two tables emp and dept
sql>select emp_id from emp;
emp_id
------
123
345
6782
32
[code].....
I would like to search all the employee id's which are present in description column of dept.
View 32 Replies
View Related
Apr 19, 2010
I am trying to write a pl/sql script where i need to check pattern matching numbers.My database is oracle 10g and i will put this logic in a procedure.i will pass no of tel_no to get.
if the no is 3 then i need 2072860126, 2072860127 and 2072860128(i.e all 3 in sequence)
if 2 then 2072860126, 2072860127. as such..the selected nos must be in sequence.
A query returns list od tel nos. in that tel i need to choose which staisfy my criteria.
View 1 Replies
View Related
Dec 20, 2010
i had a requirement to generate unique numbers without sequences.
For Example: i had a table called "Test".Test has two columns.
ID and Name are 2 column names.Primary key constraint exists on column(ID).
How to generate the ID values without using sequence and Stored procedures.
View 9 Replies
View Related
Mar 20, 2013
My management is wanting to know the serial numbers of all the components of our two Exadata machines: one quarter-rack V2 and one half-rack V1
I can use dmidecode to get the appropriate information for comp nodes and storage cells, but not for the Cisco/Voltaire switch, nor the IB switches. I read MOS 1299791.1 and the thought of asking a DOC operative to pull out the label ‘which could damage the switch’ worries me quite a bit.
Is it still true that we are unable to obtain the serial number for the IB switches and the Cisco/Voltaire switches from the CLI of the switches themselves? Sad face.
View 8 Replies
View Related
