SQL & PL/SQL :: Show Last 4 Digits

Mar 4, 2010

I have a requirement where I have a value stored in varchar2(12), and no matter what the size of data, I should mask all the characters except the last 4

eg: - 23115645

after masking should be - xxxx5645,

create table test1(x varchar2(12))
insert into test1 values('34567745')
/
insert into test1 values('438765311')
/
insert into test1 values('1112345')
/
commit

[Code].....

y should store value of x in such a way that no matter what the size of x is (7 characters or 9), we should see only the last 4, but the remaining should be masked with xxxx

I tried the above update, but using substr, i am able to get this if x has 9 characters

not sure how to achieve this if x has 7, or 4 characters

View 22 Replies


ADVERTISEMENT

Application Express :: To Change The Default Behavior Of Hide / Show Region To Show

Jun 26, 2012

I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.

View 16 Replies View Related

How To Convert Hex To Decimal For Each 2 Digits

Dec 6, 2008

Actually I got a data in hex values and i want to convert it to datetime format. But before i convert the hex value into datetime format, i must convert the hex values into decimal val first for each 2 digits of hex values. This is an example of the input data:-

This is input data in hex value:-
STARTTIME : 080b1317021a

This is decimal value after convert from hex to decimal:-
STARTTIME : 081119230226

My problem is how could i convert hex to decimal in oracle? Below are my coding:-

case
when substr(LOAD_NGNSM.STARTTIME, 1, 2) not between '00' and '99'
or substr(LOAD_NGNSM.STARTTIME, 3, 2) not between '01' and '12'
or substr(LOAD_NGNSM.STARTTIME, 5, 2) not between '01' and '31'
or substr(LOAD_NGNSM.STARTTIME, 7, 2) not between '00' and '59'
or substr(LOAD_NGNSM.STARTTIME, 9, 2) not between '00' and '59'
[code]....

View 5 Replies View Related

PL/SQL :: Replace The Digits After A String CVV With X?

Jul 13, 2012

I am using the oracle 10g, I am trying to write the a sql query by which in a below mention patterns I can replace the digits after a string 'CVV' with X. The no of X will be equal to the no of digits after CVV

1. BTA CVV 8810
2. VISA PARTICULAR CCVIXXXXXXXXXXXX5474/1012 CVV498
3. C***CVV VI 569***
4. dskdfjdkgjdfk: FP CCVI,XXXXXXXXXXXX0031/0711/CVV 063/dffddf:dfdfdfd
5. (T) CVV 4671
6. MS-ACEEML/CVV
7. O/AXXXXXXXXXXXX1007EXP1210/CVVXXXXXXXXXXX4664/MEETING CARD FOR AMEX PARTNER OFFICES

AFTER a change above data should look like as mentioned below

1. BTA CVV XXXX
2. VISA PARTICULAR CCVIXXXXXXXXXXXX5474/1012 CVVXXX
3. C***CVV VI XXX***
4. dskdfjdkgjdfk: FP CCVI,XXXXXXXXXXXX0031/0711/CVV XXX/dffddf:dfdfdfd
5. (T) CVV XXX
6. MS-ACEEML/CVV -- no change for this
7. O/AXXXXXXXXXXXX1007EXP1210/CVVXXXXXXXXXXX4664/MEETING CARD FOR AMEX PARTNER OFFICES --- -- no change for this

Initially I tried to find the position of CVV in any string and then from that position take the digit and replace them with X. I tried the below code which is not complete

select REGEXP_INSTR('BTA CVV XXXX','CVV',1,1) from dual;

View 11 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

Create Interactive Program To Add Numbers Between Two Given Digits Using SQL

Aug 11, 2008

How Do I Create An Interactive Program That Will Add Numbers Between Two Given Digits Using Sql. For Example:

Enter Start Num: 0
Enter End Num: 15
Then It Does 1+2+3+...........+14

View 2 Replies View Related

SQL & PL/SQL :: Update Table Removing First 2 Digits From Column?

Apr 10, 2013

I have a table like:

0035987850 P
0035987851 P
0035987852 P

I would like to update removing 00 in the first column. So after update to have:

35987850 P
35987851 P
35987852 P

View 5 Replies View Related

SQL & PL/SQL :: Selecting Numbers That Has Only 3 Digits After Decimal Point?

Mar 10, 2011

How to write SQL query for selecting all the values (numbers) in the table which has 3 digit after decimal point.

For example say A is the table it has following values

200.24
300.456
123.22
1234.344

From this I need only

300.456
1234.344

View 14 Replies View Related

Reports & Discoverer :: Digits Conversion - Report Developed In Builder

Oct 9, 2012

I am facing an issue here at my workplace, there is a report developed in report builder , the user wants to see the digits in arabic.

View 4 Replies View Related

Forms :: How To Force User To Enter Exactly 3 Digits Into Number Field

Mar 28, 2011

There is a text item on my form with NUMBER data type. I want to force the user to enter exactly 3 digits in to that text item. My current FORMAT MASK is set to 990. But this allows user to enter even a single digit where as my requirement is user should enter exactly 3 digits. Can we achieve this by changing the FORMAT MASK property?

View 6 Replies View Related

SQL & PL/SQL :: Row Value Show In Column Wise

Mar 1, 2012

I have a table which have three column.

1) empid
2) date
3) status

I want to show the value empid intime outime.

select in1.empid,in1.atttime,out.atttime from (select empid ,atttime from attend
where status=1 and to_char(atttime,'mmyyyy')='022012' order by empid,atttime desc) in1,
(select empid,atttime from attend where status =0 and to_char(atttime,'mmyyyy')='022012'
order by empid,atttime desc) out
where in1.empid=out.empid and in1.empid='02256'
order by in1.atttime,out.atttime

But this query do .one value relation with all column.means first february in time with all out time.

View 1 Replies View Related

Forms :: Reports Does Not Show Any Value

Feb 17, 2011

i am using oracle 10 forms ! in report i have used form Query "select sum(sale_price) from (table) expense. i attached it to oracle forms but at that time it does nt show any value!

View 3 Replies View Related

SQL & PL/SQL :: How To Show Nanoseconds Of CURRENT_TIMESTAMP

Jan 20, 2011

I am using 10g database on Microsoft Windows family OS

when i run following query

sql> SELECT CURRENT_TIMESTAMP FROM DUAL;

then i get result

20-01-2011 11:52:17.921000 AM +05:00

but i want to get result as

20-01-2011 11:52:17.921256 AM +05:00

is there any way to get up mentioned result.

View 1 Replies View Related

SQL & PL/SQL :: Want To Show Data Quarterly

Apr 8, 2013

i want to show data quarterly.My quarter structure is 3 mOnths.

q1 - apr-2013 to jun-2013
q2 - jul-2013 to sep-2013
q3 - oct-2013 to dec-2013
q4 - jan-2014 to apr-2014

I input parameter is from date and to date..

Suppose user enter from date - '01-apr-2012' and to date - '01-aug-2012',
only q1 shows..

and user enter from date - '01-apr-2012' and to date - '30-sep-2012',
only q1,q2 shows..

how i do that..My data is like this

create table t1( t1_month VARCHAR2(10),
t1_val1 number(10),
t1_val2 NUmber(10)
);
INSERT INTO t1('APR-13',100,200);
INSERT INTO t1('MAY-13',100,200);
INSERT INTO t1('JUN-13',100,200);
INSERT INTO t1('JUL-13',100,200);
INSERT INTO t1('AUG-13',100,200);
[code]...

View 2 Replies View Related

SQL & PL/SQL :: Show Result Random

Mar 24, 2010

I run follwing query.

select * from crp_00_08

it shows result like

BAS_CODBAS_DES

1MAIN GATE (FRONT)
2NEAR MILL # 3, 3-1
3NEAR MILL # 3, 3-2
4NEAR MILL # 3, 3-3

[Code]...

i want that when i run above query every time bas_cod column should start with difference value. now it is starting from 1, i need when i run next time it should start with another number except 1,

i mean whenever i run above query,order of bas_cod must be change from last.

how can i do it.

View 12 Replies View Related

SQL & PL/SQL :: Show End Date Based On Next?

May 19, 2010

How to write a single query for the below scenario?I got only start date column, but I need show the end date based on next (start date-1). For the last row, need to show the same date in the end date column.

For Example:I have the data as shown below,

Start date End date
1-Jan-10
3-Mar-10
3-May-10
5-Jul-10
1-Aug-10

I wanted to output as shown below

Start date End date
1-Jan-10 2-Mar-10
3-Mar-10 2-May-10
3-May-10 4-Jul-10
5-Jul-10 31-Jul-10
1-Aug-10 1-Aug-10

View 10 Replies View Related

SHOW TABLES In Oracle?

Mar 31, 2004

In MySQL, I can get a list of all tables w/ this query: "SHOW TABLES"

Whats the syntax w/ Oracle 9i ?

Looked through the docs online, but w/o knowing what you're trying to look up, you can't really look anything up (kind of a catch22).

View 9 Replies View Related

PL/SQL :: Not Show Last Row Of Test Number?

Feb 7, 2013

I have below mentioned records in mount so whenever i select any mount it should now show the the last row of that mount i.e. test no-3 any records of mount which will not show last row of test number

mount     day     org1 test_No org2 org3     org4
299.00 1     0     1     1     0     7186
299.00     1     0     2     1     0     7186
299.00     0     3     3     0     7186

[code]...

but i want test_no should be in asceding order

View 3 Replies View Related

11g RAC Listener Show Only One Instance?

Jan 31, 2013

Though I have it all configures:

On node1:
SQL> show parameter listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.28.155.79)(PORT=1521))))

[code]....

View 9 Replies View Related

Show Parameter Audit_trail And Function

Dec 16, 2010

Is there any way to get audit_trail.VALUE? I know the statement: show parameter audit_trail; can show that value. But, i want to build a function which will return a string that contain audit_trail.VALUE

View 1 Replies View Related

SQL & PL/SQL :: How To Show Sequential Numbers For Each Group

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

SQL & PL/SQL :: Show Missing Rows In Query

Feb 7, 2012

SQL> select * from t1;

NAME
----------
JACK
JOHN
JENN

SQL> select * from t2;

NAME
----------
JACK
PAUL

SQL> select a.name from t1 a, t2 b where a.name <> b.name
2 order by name;

NAME
----------
JACK
JENN
JENN
JOHN
JOHN

I would have expected to see the following:

name
-----
JOHN
JENN
PAUL

how to fix this query? In additon, is there a way to print the table name or some arrows (>>, <<) to show which table the values came from

I.e

name
=====
JOHN t1 or <<
JENN t1 or <<
PAUL t2 or >>

View 14 Replies View Related

Performance Tuning :: How To Show Right IO Of Query

Jun 21, 2011

I use oracle 11g r2

i have created a table for objects

create table table_t
(Record_no number,
t_no number,
position SDO_GEOMETRY
, occupation_time number);

and inserted many values (examples below )

INSERT INTO tra VALUES
(2,
20503,
SDO_GEOMETRY
(2001,
NULL,
SDO_POINT_TYPE (1387, 0, NULL),
NULL,
NULL),
23037
)
/

and( position) indexed as Rtree spatial index

now when i run spatial query such as

SELECT * FROM tra t WHERE
SDO_FILTER(t.position, MDSYS.SDO_GEOMETRY(2001,NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(0,0,9000,0)), 'querytype=WINDOW') = 'TRUE' and t.position.sdo_point.X=1;

i do not know how many IO accrued ?

i tried set autotrace on

but the physical read is 0 , this is not possible because i have more than 100000 objects there and all indexed as R-tree

View 2 Replies View Related

SQL & PL/SQL :: How To Show Unique Record Only In Query

Jun 20, 2012

I am getting a following odd result where the same a Job could be linked to multiple enquiries. What I want it to show is just one job if its linked to multiple enquiries. I show you some sample result:I wrote the following SQL for it:

SELECT
'Enquiry' as Origin,
job.job_number,
enquiry.enquiry_number as Defect_or_Enquiry,
job.site_code,
central_site.site_name,
job.priority_code,
priority.priority_name,
job.target_comp_date,
'Target Completion Date required' as Due_Date
[code]....

Is there anything you would add so that it shows just a unique job number where the job has multiple enquiries linked to it? I tried putting SELECT DISTINCT but that did not work.

View 11 Replies View Related

SQL & PL/SQL :: How To Show Only Address For Yes Tax Reporting Site

Aug 22, 2013

I have a sql to pull all payments from vendors for a specific time period; however, now tasked to only show the Address of each Tax Reporting Site. I have tried several commands but have not been successful.

View 7 Replies View Related

SQL & PL/SQL :: Show Date In Form With Abbreviated Name Of Day

Dec 1, 2010

i want to show the date exactly in following format:

00:16:15 Wed 1st Dec 2010

I have tried :
select to_char(to_date('01/12/2010 00:16:15' , 'dd/mm/yyyy hh24:mi:ss'), 'hh24:mi:ss Day dd Mon yyyy')
from dual

Result it is giving
00:16:15 Wednesday 01 Dec 2010

How to show date in form 1st 2nd 3rd and so on?
How to show Abbreviated name of the day?

View 13 Replies View Related

Forms :: How To Show Sql Statements Generated

Jul 19, 2010

Is there a way to show the sql statements generated by the Forms?

View 4 Replies View Related

SQL & PL/SQL :: How To Show Total Number Of Department

Apr 19, 2013

how to show total number of department with their department name assign to employee table.

View 2 Replies View Related

SQL & PL/SQL :: External Table Does Not Show All Records

Jun 24, 2010

I have an external table as follows:

CREATE TABLE EXT1
(
COL1 NVARCHAR2(2000),
COL2 CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY FILE_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
( "COL1" CHAR,
[code]....

From here I can count all records and the total number of records matching the file record count, but could not read the clob columns. it's not picking up the values. but if I take this out

( "COL1" CHAR,
"COL2" RAW
)

then I am missing the record counts.Also if you put all varchar and char, that also missing the count against the file.

View 6 Replies View Related

SQL & PL/SQL :: Query To Show Only Transactions For Today

Sep 12, 2010

I need to create a report that will show all activity for the day that the report is run. The report will be run or auto-refreshed throughout the day. So, at 10am, 11am, 2pm, 4pm etc. I just need to display quantities received, shipped etc. for that same day. Since the report will not be run at a fixed time, I can't use sysdate - .5 for example.

View 6 Replies View Related







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