SQL & PL/SQL :: How To Find Nearest / Closest String For A Column Data

Jun 1, 2011

Quote: I have a table(table name is names) with column as name(varchar) . I have the following data for name column.

Miss
Mississ
Mississipp

I would like to find a nearest match for Mississippi, that means sql should return row that contains Mississipp( Row #3)

If I try to find nearest match for Mississirr then sql should return row that has column value Mississ (Row#2)
Is this possible ? Here is the code for table creation and data.

create table names (name varchar2(20));

insert into names values('Miss');
insert into names values('Mississ');
insert into names values('Mississipp');
commit;

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Find Nearest Integer / Where Mod Returns 0

May 24, 2012

I want to find nearest integer value where mod returns 0 in sql statement. I've tried following but it doesn't fulfill my requirement.

My Try

SQL> select
2 ((1200*1000)+45-mod((1200*1000),45)) f1,
3 mod( ((1200*1000)+45-mod((1200*1000),45)),45 ) f2,
4 ((1200*1000)+45-mod((1200*1000),45))/1000 f3
5 from dual;

F1 F2 F3
---------- ---------- ----------
1200015 0 1200.015

In above result F3 represent the actual result, which is nearest value where mod returns the 0, but i want nearest integer value which is 1206. how it is possible. In above case consider 1200 as Kgs and 45 as Grams.

View 20 Replies View Related

SQL & PL/SQL :: How To Find Next Closest Number In Series

Jan 21, 2012

i have a data something like

010030
110495
210960
311425

[code]...

and user enters the figure like 13025now i would like to find the closest next value to the number entered by user, which is 713285 .how can i get this result, i have search a lot on Internet and i found some analytic functions can work out. but don't know how ..

View 14 Replies View Related

PL/SQL :: Closest Date Missing Data

Jul 19, 2012

My requirement is to find the closest date from col B(a.p_date) to the dates in col A(d.p_date). I got the following which works great:

SELECT
d.p_no_s,
d.p_date,
MIN(a.p_date),
MIN(a.p_date)-d.p_date||' Number of Days' NUM_OF_DAYS

[Code]...

Output:

p_no_s p_date MIN(a.p_date) MIN(a.p_date)-d.p
-------------------- ---------- ---------- -------------------------------------------------------

Z1575560 15/06/2008 29/07/2008 44 Number of Days
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008 27/12/2008 36 Number of Days
Z1575560 17/12/2008 27/12/2008 10 Number of Days

Problem:

For 1st and 2nd row,

I am getting 29/07/2008 as a.p_date for both 15/06/2008 and 15/07/2008 which is wrong in my scenario. This is because data is missing in the second table for row 1 (similarly for row 3). So What I want is :

Z1575560 15/06/2008
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008
Z1575560 17/12/2008 27/12/2008 10 Number of Days

Say for eg: a person is in city A. He is departing on (15/06/2008 ) and arriving on 29/07/2008 and again departing on 15/07/2008 which is not possible.

It should be departing on (15/06/2008 ) and arriving between 15/06/2008 and 15/07/2008 (missing data hence null is required here) and departing on 15/07/2008 and arriving on 29/07/2008 .

let me know how to achieve this.

View 4 Replies View Related

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

Mar 14, 2013

I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.

For ex:

The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.

writing that piece of code to append the value fetched which doesn't exactly match with the existing string

View 3 Replies View Related

PL/SQL :: SQL For Rounding To Nearest Value

Oct 3, 2012

My Oracle DB version is 10g Release2 and also 11gR1

I have data like below, I need to write a SQL for the below requirement.

ID   RANGE_LOW                RANGE_HIGH
--    ------------------     -------------------
1      50                    55             
2      55                    60
3      60                    63 
4      63                    77 
5      77                    84  

The requirement is like I need to check a value between the above range low and high then round it to the nearest value.

Say, for example if I have 68 then the value is rounded to 63 else if 74 then 77 else if its in the middle of two values (70) then rounded to the highest value, here its 77.

View 4 Replies View Related

PL/SQL :: Round The Number To Nearest 10k

Nov 1, 2012

I am facing small issue, i need to round the number to the nearest 10k for some finance reporting

ex
1. if number is 2250 round to 10,000
2. if number is 5500 round to 10,000
3. if number is 4912345 round to 4920000
etc..

the number should always round up to nearest 10k..

I am using round function to achieve this..

case 1:-
SQL> Select round(2250,-4) From dual;

ROUND(2250,-4)
--------------
             0

I need 10,000 here..

case 2:-
SQL> Select round(5500 ,-4) From dual;

ROUND(5500,-4)
--------------
         10000

SQL>

This is working fine...

case 3:-
SQL> Select round(4912345 ,-4) From dual;

ROUND(4912345,-4)
-----------------
          4910000

This should round UP to 4920000

let me know if there is any way i can achieve this...

View 10 Replies View Related

SQL & PL/SQL :: Find A Match Within String And Compare?

Aug 2, 2012

Below is a column 'ADDR' with the data (single column)

ADDR
--------
/shared/Folder_1 :^BIAdministrator:^BIAuthor:^BIConsumer:BISystemUser:OracleSystemUser:System:weblogic: :F

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: How To Manipulate String (Find And Replace)

Jun 29, 2012

I am required to manipulate a string in this way.For example ABCDEF)* to $ABCDEF). So I should find * and delete it and instead put $ in the beginning of string.

View 9 Replies View Related

SQL & PL/SQL :: How To Find Greatest Values In A String

Jul 22, 2011

I am trying to update the greatest value in a column from a string of other column.

Ex: f the value is shown 10M+16M+25M-DG, then populate 25 only

so for that I had written query as follows:

update ANCHOR set IEL_STRAND_SIZE= greatest(
substr
(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','),
0,
length(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','))-1))

The output is given as 10,16,25,but not as 25.so how could i write it?Do I need to implement procedure or arrays for it.

View 7 Replies View Related

SQL & PL/SQL :: Find Occurrences Of Some String In DB Objects?

Jul 11, 2012

How can i identify all the occurences of raise_application_error(-20XXX, '<the message>'); within all database objects, and replace them with other text?

View 4 Replies View Related

SQL & PL/SQL :: Find A Negative Number In String?

Apr 24, 2012

I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.

One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.

This is the code I am currently trying on and is not looking good.

SELECT 1 FROM DUAL
WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'

View 13 Replies View Related

SQL & PL/SQL :: Way To Find Out Ending Digits In A String?

Dec 5, 2012

Is there a way to find out the ending digits in a string?

Examples of input and outputs:
'ABC123' -> 123
'ABC' -> NULL
'123ABC' -> NULL
'123ABC456' -> 456
'123ABC456QP98' -> 98

I have figured a way to do it by doing Reverse and re-reversing using: reverse(regexp_substr(reverse(p_string), '[ [:digit:]]*'))

But I'm sure there is a way to do it backwards without using reverse function, which I am not able to properly put in the syntax.

View 3 Replies View Related

SQL & PL/SQL :: How To Find Out Lowercase Char In A String

May 16, 2008

How to find out lowercase char in a String in a SQL query

for example

"ORAclE" here c and l are lowercase so how can i find out this condition... is there any build in function in oracle?

View 13 Replies View Related

SQL & PL/SQL :: How To Find Any Character Is Repeating In A String

Sep 8, 2011

How to find whether any character in a string is repeating or not

Eg: '123LH563' should return 'YES' , as 3 is repating there
'1234567' sould return 'NO' as there is no character which is repeating

View 8 Replies View Related

PL/SQL :: Find And Replace Value In Delimited String

Sep 4, 2013

I have a requirement, where i need to find and replace values in delimited string. For example, the string is

"GL~1001~157747~FEB-13~ CREDIT~ A~N~ USD~ NULL~".

The 4th column gives month and year. I need to replace it with previous month name. For example:

"GL~1001~ 157747~ JAN-13~ CREDIT~ A~N~USD~NULL~".

I need to do same for last 12 months. I thought of first devide the values and store it in variable and then after replacing it with required value, join it back. I just wanted to know if there is any better way to do it?

View 10 Replies View Related

SQL & PL/SQL :: Find Packaged Procedures Names For Particular String

Nov 7, 2011

I want to search for some specific string in packaged procedures source code, and want to list the name of those procedures along with package names.

View 17 Replies View Related

SQL & PL/SQL :: How To Find Extended ASCII Characters From A String

Sep 19, 2013

I have a table like mentioned below

create table test1( test_no number, test_description varchar2(100));
insert into test1 values (1,'ABC£¥');
insert into test1 values (2,'BCD£¥');

Now I am selecting from the above table and the expected rest it should have shown is as shown below

TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABC£¥
2 BCD£¥

But instead of showing the extended ascii characters, it is showing some different characters as shown below

select * from test1;

TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABCLY
2 BCDLY

I have an requirement where i need to fetch the exact ascii characters for £ and ¥ instead of L and Y respectively.

View 1 Replies View Related

SQL & PL/SQL :: Selection Of Next Closest Day

May 5, 2011

I am trying to develop an ongoing interview schedule.

interviews may be held on FRIDAY, SATURDAY, TUESDAY or any other day or Days in a every week selected by the user and also at some specific time.

we need to schedule the closest comming day based on Application date.

for example: today is WEDNESDAY and our schedule interview day is FRIDAY and SUNDAY. If one application is received today we need to schedule interview on FRIDAY because this is the nearest/closest day of WEDNESDAY. and if the application is received on Saturday then of course Sunday will be scheduled.

if Application is received on same day e.g. FRIDAY is scheduled and we receive Application on FRIDAY then comes the concept of time. interview is schedules on FRIDAY 11:30 AM.

if application is received on before 11:30AM, the same day will be scheduled. If application is received after 11:30 AM then of course next closest day will be scheduled.

View 1 Replies View Related

PL/SQL :: Get The Closest Max Date

Jun 27, 2012

I have a table named UFM with columns :

trans_ID,Plz_id,clas,trans_date
31106952318303-     005110-2-     6/24/2012 5:56:07.800000 AM
33706952318401-     005110-     2-     6/24/2012 5:56:23.873000 AM
34806952318304-     005110-     2-     6/24/2012 5:56:25.650000 AM
35406952318442-     005110-     2-     6/24/2012 5:59:20.260000 AM

[Code]...

I have another table named facility with columns:

plz_id, Eff_date
005110-     1/10/2012 10:00:00.000000 PM
005110-     1/11/2012 12:00:00.000000 AM
005110-     1/13/2012 12:00:00.000000 PM
005110-     6/24/2012 6:00:00.000000 AM

I want all and records from UFM joining to facility table, i need columns trans_ID,Plz_id,clas,trans_date,Eff_date in my output.

the output should look like ,

trans_ID,Plz_id,clas,trans_date,Eff_date
31106952318303-     005110- 2-     6/24/2012 5:56:07.800000 AM-1/13/2012 12:00:00.000000 PM
33706952318401-     005110-     2-     6/24/2012 5:56:23.873000 AM-1/13/2012 12:00:00.000000 PM
34806952318304-     005110-     2-     6/24/2012 5:56:25.650000 AM-1/13/2012 12:00:00.000000 PM
35406952318442-     005110-     2-     6/24/2012 5:59:20.260000 AM-1/13/2012 12:00:00.000000 PM

[Code]....

for a transaction that occured on 6/24/2012 after 6:00 AM the eff_date should be 6/24/2012 6:00:00.000000 AM and for a transaction before 6:00 AM on 6/24/2012 the eff_date should be 1/13/2012 12:00:00.000000 PM. and the transactions that occured before 1/13/2012 12:00:00.000000 PM the eff_date should be 1/11/2012 12:00:00.000000 AM and so on.

View 4 Replies View Related

Semantic Technologies :: Orageo Nearest Neighbor No Results?

Oct 13, 2013

 I am using Oracle 12c Spatial and Graph and I would like to retrieve the nearest neighbors of a point from a dataset encoded according to GeoSPARQL.I have created a spatial index on the datatype geo:wktLiteral and I to the query so the optimizer pick a plan using the index RDF_V$GEO_IDX. The query I pose is the following: 

SELECT geo, wkt
FROM TABLE(SEM_MATCH('
PREFIX geo: <http://www.opengis.net/ont/geosparql#>
SELECT ?geo ?wkt
    
[code]...

 I do not get any error but I do not get any results neither. However, there are a lot of points around POINT(22.39 38.25) in my dataset and if I remove the filter I get results.Do I use orageo:nearestNeighbor in a wrong way?  

View 6 Replies View Related

SQL & PL/SQL :: Value For Closest Or Equal Date

Aug 23, 2013

I have two tables e.g. test_bb and test_sub

I would like to select test_sub.price as per the below conditions. If test_bb.value_date, test_bb.vehicle matches to test_sub.value_date,test_sub.vehicle then display test_sub.price

If there is no match then as above find the closest test_sub.value_date which is less than test_bb.value_date and select corresponding test_sub.price for the same vehicle combination.

e.g.

select * from test_sub;
VEHICLE VALUE_DAT PRICE
---------- --------- ----------
10 12-APR-12 2
10 08-JAN-10 4
10 14-APR-14 6
10 06-AUG-47 8
20 24-JAN-14 10
20 20-FEB-06 12
20 18-FEB-04 14
20 28-FEB-06 16
20 22-DEC-07 19
8 rows selected.

select * from test_bb;
VEHICLE VALUE_DAT
---------- ---------
10 12-APR-12
10 10-FEB-10
20 24-JAN-14
20 22-FEB-08

Required output:
VEHICLE PRICE VALUE_DAT
---------- ---------- ---------
10 2 12-APR-12
10 4 08-JAN-10
20 10 24-JAN-14
20 19 22-DEC-07

create table test_bb(vehicle number, value_date date);

begin
insert into test_bb values(10,to_date('12-04-2012','dd-mm-yyyy'));
insert into test_bb values(10,to_date('10-02-2010','dd-mm-yyyy'));
insert into test_bb values(20,to_date('24-01-2014','dd-mm-yyyy'));
insert into test_bb values(20,to_date('22-02-2008','dd-mm-yyyy'));
end;
/

create table test_sub(vehicle number, value_date date,price number);

begin
insert into test_sub values(10,to_date('12-04-2012','dd-mm-yyyy'),2);
insert into test_sub values(10,to_date('08-01-2010','dd-mm-yyyy'),4);
insert into test_sub values(10,to_date('14-04-2014','dd-mm-yyyy'),6);
insert into test_sub values(10,to_date('06-08-1947','dd-mm-yyyy'),8);
insert into test_sub values(20,to_date('24-01-2014','dd-mm-yyyy'),10);
insert into test_sub values(20,to_date('20-02-2006','dd-mm-yyyy'),12);
insert into test_sub values(20,to_date('18-02-2004','dd-mm-yyyy'),14);
insert into test_sub values(20,to_date('28-02-2006','dd-mm-yyyy'),16);
insert into test_sub values(20,to_date('22-DEC-2007','dd-mm-yyyy'),19);
end;
/

I could write as below but I would like to know if there is a better way of doing it.

select bb.vehicle
,sub.price
,bb.value_date
from test_bb bb
,test_sub sub
where bb.vehicle=sub.vehicle
[code].........

View 8 Replies View Related

PL/SQL :: Closest Date From A Group Of Dates

Dec 19, 2012

I need to find the closest Date that matches a Particular Date. The Closest Date from the Group may be less than or greater than the Date I am trying to find.

I have two columns: VISIT_DATE and ACTUAL_DATE. The VISIT_DATE columns has many records with different dates while the ACTUAL_DATE column would only have one record per Student ID.

Here is an example of dates:

Visit Date      Actual Date
==========================
01-APR-09     19-MAR-10
16-NOV-09     19-MAR-10
17-MAR-10     19-MAR-10
21-MAR-10     19-MAR-10
04-APR-11     19-MAR-10
15-JUN-11     19-MAR-10
19-SEP-11     19-MAR-10
24-FEB-12     19-MAR-10

The closest date to 19-MAR-10 are in fact 17-MAR-10 and 21-MAR-10. I would in that case need to pick up both records.

View 4 Replies View Related

SQL & PL/SQL :: Multiple Values Of RULES Tables To Find Records Containing A String Format

Sep 4, 2013

Insert into PROFILE
(INSTANCE, PROFILENAME, USER_DATA, UPDATE_DATE)
Values
(138, 'Test A', 'SRC!-1,ARCHIVE_OPT!-1,DATE_FIELD!155,DATE_RULE!1,DISTINCT!1', TO_DATE('01/20/2005 13:35:33', 'MM/DD/YYYY HH24:MI:SS'));
/
Insert into RULES
(ID, NAME)
Values
(155, 'DATE_TEST');

I want a code something of this sort:

select profilename from PROFILE where user_data like '%DATE_RULE!115%';

Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?

View 5 Replies View Related

SQL & PL/SQL :: How To Search For A Particular Text In String Column

Oct 18, 2012

I have one table for eg. TB_Fruits.

In that i have one column FruitsName(Varchar)

In that column i am storing string in comma separated values.

Select FruitsName from tb_fruits;
Result: orange,banana,apple

Now the issue is suppose if i try to insert any of these fruits name again then it should not allow me to insert.

Suppose now if i try to insert ('grapes,banana')
or
('apple,grapes')

the orange,banana,apple can be in any position.

How to check if any of these names already exist or not in the column fruitsname?

I cannot use like or INstr function here. because the position is not fixed not even string.

View 1 Replies View Related

SQL & PL/SQL :: Convert Column Value To Delimited String Using Query?

Jan 10, 2012

I want to convert a column value to a delimited string using a query.

Example

TableA
Col1 Col2 Col3
1 x200 MIS-X
2 x200 BTS-X
3 x200 TYR-X
4 x100 YRY-X

Select Col3 From TableA where Col2 = 'x200'

Expected Output:

'MIS-X','BTS-X','TYR-X'

View 4 Replies View Related

SQL & PL/SQL :: Search A String In TEXT Column Of Dba_views

Aug 4, 2010

I want to select the view names from text column in dba_views.

View 7 Replies View Related

SQL & PL/SQL :: Read Only The Input String From BLOB Datatype Column?

Mar 28, 2013

I have a table that has column with BLOB datatype. I am trying read only the inputted String from the BLOB datatype column. I have used the below query,

SELECT utl_raw.cast_to_varchar2(column1) FROM TAB1
and it gives the result as
<H2><FONT color=#cc0000><EM><U>test</U></EM></FONT></H2>

However I would like to extract only the string "test" which is inputted. Is there any build-in function in oracle which will satisfy this requirement?

View 7 Replies View Related

Application Express :: Report String Column To Date?

Apr 22, 2013

v: Apex 4.2

I have Dynamic report and column say some_date column and data can be in the columun (01-JAN-13) OR (01-JAN-13,04-JAN-13), (01-JAN-13,04-JAN-13,10-JAN-13) so on.

in actual table column some_date is date field but in report as above (string) because using RTRIM(some_date, ',') some_date within the select MODEL clause.

my problem is when run the report and select filter from action menu then it doesn't give option such as < , > or so on because of string but I want it should give <, <=, >, >= operators too as date column.

View 5 Replies View Related

Storing Large Volume File In CLOB Column As A String?

Oct 6, 2009

I am having problem in inserting a long file (80000 B) into CLOB cloumn in oracle database 10g.

ERROR at line 23:
ORA-06550: line 23, column 1:
PLS-00172: string literal too long

I am calling the oracle stored procedure through unix shell script as fallows.

#!/bin/ksh
cd /usr/home/dfusr/backup
i=`cat pe_proxy_master_2160.Thu.log | sed "s/'/''/g"`
sqlplus username/password@db 1> temp.log <<!
DECLARE
BEGIN
Write_Text_To_CLOB(1,'$i');
COMMIT;
END;
/

The file pe_proxy_master_2160.Thu.log is about 50000 B.

My Stored Procedure is fallows

CREATE OR REPLACE PROCEDURE Write_Text_To_CLOB (
p_id IN NUMBER
, p_clob IN VARCHAR2
)
IS

[code]....

I also tried with ojdbc5.jar and ojdbc14.jar files in class path.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved