SQL & PL/SQL :: Function For Allowing Only Numbers And Characters
Jan 10, 2011Need to write a function that should allow only characters and numbers in Login ID using pl sql.
View 7 Replies
ADVERTISEMENT
SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers
Jul 13, 2011I 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
SQL & PL/SQL :: Query To Get Whether A String Contains 2 Upper Characters And Numbers Using Regular_exp
Jun 12, 2013A sample query to check whether a string contains 2 upper characters and numbers are present in that character using regular_exp .
View 12 Replies View RelatedSQL & PL/SQL :: Order By Field Containing Characters And Numbers (split From Convert No Rows Returned To Zero)
Jan 13, 2012I Want to make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.
View 2 Replies View RelatedSQL & PL/SQL :: Function To Sum Five Numbers
Apr 13, 2013I 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;
SQL & PL/SQL :: How To Print Even Numbers Without Using MOD Function
Mar 1, 2012How to print even numbers 1 to 10.without using the MOD Function
View 17 Replies View RelatedPL/SQL :: Two Consecutive Numbers Are Transposed Function Returns True
Jul 6, 2012oracle version 11gr2.In the below sample data column a,b if there is Two (2) consecutive numbers are transposed in column B from A it should return string 'true'.
with t as
(
select '123456789' a ,'123476581' b from dual
union all
select '123456789' ,'123465789' from dual
union all
select '332211' ,'332121' from dual
union all
[code]....
SQL & PL/SQL :: Read International Characters Through Utl_file Function
Jul 19, 2010i am using oracle 10g enterprise edition.whether its possible to read international characters from the text file using the utl_file function..?
View 1 Replies View RelatedFunction To Convert Special Characters To HTML Code?
Nov 5, 2008The HTML code for the bracket character '[' is & # 91; (without spaces). In my SQL, I would like to convert the word [you into & # 91;you. Is there a way to do that?
I know that replace() will work, but with that you have to supply a list of chars to replace; I
SQL & PL/SQL :: Function Code To Restrict User From Entering More Than 30 Characters
May 24, 2011Need a code which prevents the User from entering more than 30 characters in a field. Although the variable can accept more than 30 characters. My requirement is to validate the field by restricting up to 30 characters.
View 6 Replies View RelatedPL/SQL :: Translate Function Remove Alpha Characters From String
Feb 7, 2013I 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
SQL & PL/SQL :: Allowing Only Email Address In The Field
Jan 23, 2011I want only one email address should be entered in the Alphanumeric field and not any words or phrases or spaces or any other email address.
How to give restriction for this.
Oracle Listener Log Entries Missing And Database Not Allowing Connections
Mar 20, 2013We have a custom portal application that uses a bunch of application servers. Our connection pool max size is 1100. For the past two days, we had two incidents when the cpus reached the max limit and the db was not allowing connections to come thro' for 10 minute periods. The alert log does not have any entry and the listener log does not have any entry for that 10 minute period.
View 5 Replies View RelatedSQL & PL/SQL :: Removing Special Characters And Get Desired Characters From Column Values
Jul 23, 2013create table test
(
name varchar2(50),
descd varchar2(50)
)
insert into test values ('kethlin','da,dad!tyerx');
insert into test values ('tauwatson','#$dfegr');
insert into test values ('jennybrown','fsa!!trtw$ fda');
insert into test values ('tauwatson','#$dfegr ,try');
how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?
so my o/p be like;
Quote:('ketlin','dadadtyerx')
('tauson','dfegr')
('jenown','fsatrtw fda')
('tauson','dfegr try')
String Of Numbers
Oct 12, 2007I 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!
SQL & PL/SQL :: How To Get Random Numbers
Jul 1, 2010I 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 ?
SQL & PL/SQL :: How To Get Column Containing Numbers Only
Jul 25, 2010I 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
SQL & PL/SQL :: Combinations Of Numbers
Nov 1, 2010Earlier, 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
SQL & PL/SQL :: Selecting 1st And 2nd TOP Numbers (from Same Row)
Jul 6, 2012I 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
SQL & PL/SQL :: Numbers To Alphabets
Jul 6, 2013I 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
SQL & PL/SQL :: Numbers To Words
Jul 6, 2012I 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>
PL/SQL :: Extract Numbers Only
Sep 18, 2012Here 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.
Insert All Client Numbers
May 12, 2010I 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
SQL & PL/SQL :: Checking Consecutive Numbers
May 11, 2012I 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
SQL & PL/SQL :: Spell Out Numbers To Words
Mar 14, 2002I 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 ???)
SQL & PL/SQL :: Missing Numbers In A Sequence
Feb 16, 2010create 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
Forms :: Calculation When Add Numbers
Feb 5, 2011I 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
SQL & PL/SQL :: Find Numbers Between More Than 1 Condition
Sep 21, 2011I 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.
SQL & PL/SQL :: Assigning Numbers To The List?
Apr 10, 2012acheive 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
.. ....
SQL & PL/SQL :: No Rounded Decimal Numbers
Aug 5, 2011how 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
