PL/SQL :: Regular Expression To Check Number With At Least One Decimal Point?

Nov 18, 2013

I would like to use the REGEX_LIKE to check a number with up to two digits and at least one decimal point: Ex.10.11.1112 This is what I have so far.

if regexp_like(v_expr, '^(d{0,2})+(.[0-9]{1})?$') t

View 3 Replies


ADVERTISEMENT

PL/SQL :: How To Perform A Pattern Check - Regular Expression

Oct 16, 2012

Is there a way to perform a pattern check on a value ?

For example: 654321HD9

The pattern is 6 numbers, followed by 2 letters, followed by 1 number. The data type for the attribute is a string.

Examples of right or wrong
654321HD9 - correct value
654321HD - wrong value
654321111 - wrong value
HD1111111 - wong value

The pattern has to be as i mentioned above (6 number, 2 letters, 1 number) otherwise its wrong. My pattern does not cover all cases.

select REGEXP_SUBSTR('654321HD9', '[0-9]+[A-Za-z]+[0-9]+') from dual;

View 6 Replies View Related

Fix Number Of Digit After Decimal Point?

Mar 21, 2007

I have a small prob. I want an amount to be always 2 decimal places. I've used the Round function - Round(amount,2). the problem is that if the amount is only to 1 dp like 1.4. the above function will return 1.4. I want it to appear like 1.40

View 2 Replies View Related

SQL & PL/SQL :: Function That Multiply Number When There Is More Than 3 Decimal Point?

Mar 20, 2010

how to create a function that will multiply the number when the number has more than 3 decimal point.

Example.
123.012 - multiply by 10
123.0123 - multiply by 100
123.01234 - miltiply by 1000

Column A is the result of the number being multiplied according to decimal point.
Column B contains the multiplier used in column A.

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

PL/SQL :: Round Function Output Depend On 2nd Digit After Decimal Point

Oct 19, 2012

I have a question on Round Function

Round(###.###,1)

Will the round function output depend on 2nd digit after decimal point also or not?

View 6 Replies View Related

SQL & PL/SQL :: Regular Expression In Oracle 10g

Apr 13, 2013

What is the use of Regular Expression?

View 1 Replies View Related

SQL & PL/SQL :: How To Use Substr Or Regular Expression

Jan 21, 2011

I have a following table,

CREATE TABLE checkdata
( col1 VARCHAR2(2000)
);
INSERT
INTO checkdata VALUES
[code]......

I need output as ,
INT8144925446-20110118T123723Z
INT8144925448-20110118T123730Z

How do i use Substr and Regular expression here ?

View 11 Replies View Related

PL/SQL :: Regular Expression Select

Jun 6, 2012

Sample Data:

SELECT 'A/BCD/CCE/DFFFF' test  from dual
Expected Output:

SELECT 'A' A,
'BCD' B,
'CCE' C,
'DFFFF' D
FROM DUAL;  

View 6 Replies View Related

PL/SQL :: Regular Expression Matching All Given Words

Nov 8, 2012

I'm trying to match all sentences that contain words starting with given search tokens at least once. For example: if the given search token words are one and two then only sentences like "one plus one is two" should match. And should not match sentences like "one plus three is four". I was able to come up with this but I need a AND condition which I'm unable to get it right.

select count(*) from dual where regexp_like('one plus one is two', '(^|s)one|three', 'i');

Currently this gives a count of 1. But needs to give a count of 0 when the regexp is fixed.

search words : one two

one is less than two -> match
two is greater than one -> match
onetwo is union of two numbers -> match
onetwo is union of 2 numbers -> not a match as 'two' is not at the beginning of a word
one is less than three -> not a match as two is not present.

View 7 Replies View Related

SQL & PL/SQL :: Remove Special Chars Using Regular Expression

Apr 9, 2013

Can we use regular expression to eliminate all characters other than -

A to Z
0 to 9
Special characters like - &,%,~,@,#,$,^,*,_,+,=,,/,<,>

Example

String - TEST@#_~````}{!!!12311HELLO

Expected result - TEST@#_~12311HELLO

how we can use regular expression to achieve this result.

View 4 Replies View Related

SQL & PL/SQL :: Finding Date Value In Given String By Using Regular Expression

Jul 3, 2012

I have a Identifier column with start and stop dates along with description .

Two dates are separated by '-'. But the position of that character(-) is not constant always. Depending on the instr function I am able to divide the start and stop dates. But I am getting the performance problem because of huge data

I think the same logic will be implemented by regular expression also . How to write the equivalent logic by using regular expressions

create table REG_EXP_TEST
(
TRANS_ID NUMBER(10),
TRANS_IDENTIFIER VARCHAR2(250)
) ;
insert into REG_EXP_TEST
(TRANS_ID, TRANS_IDENTIFIER)
values
[code].......

View 3 Replies View Related

SQL & PL/SQL :: Use Of Regular Expression In Real Time Scenario

Jul 24, 2012

Regular Expression in oracle??Especially the use of this in real time scenario.

View 4 Replies View Related

PL/SQL :: Regular Expression To Remove Space In HTML Tag?

Nov 6, 2013

, My HTML string is like below. select '<CityName>RICHMOND</CityName>  <StateCd>ABCD CDE  <StateCd/><CtryCd>CAN</CtryCd><CtrySubDivCd>BC</CtrySubDivCd>' Str from dual Desired Output is<CityName>RICHMOND</CityName><StateCd>ABCD CDE  <StateCd/><CtryCd>CAN</CtryCd><CtrySubDivCd>BC</CtrySubDivCd> i.e.

want to remove those spaces from tag value area having only spaces otherwise leave as it is.implement the same using Regular expression.

View 2 Replies View Related

SQL & PL/SQL :: Regular Expression To Find Commits In All Oracle Source?

Oct 14, 2013

I would like to write a select that would return all places in DB that are commiting transaction.

E.g. package for testing:

CREATE OR REPLACE PACKAGE test.TEST_COMMIT AS
PROCEDURE THE_ONLY_COMMIT_IN_DB ;
END TEST_COMMIT;--not a match
CREATE OR REPLACE PACKAGE BODY test.TEST_COMMIT AS

[code]....

The select should return 4 rows with --ok.

SELECT *
FROM ALL_SOURCE ASO
WHERE
REGEXP_LIKE(ASO.TEXT,'commit(s*);','i' )
AND name = 'TEST_COMMIT'

View 18 Replies View Related

JDeveloper, Java & XML :: Regular Expression For Exact Word And Occurrence

Mar 15, 2012

I need to search a specific pattern from a source code. In word, I need to check whether "getCode" has been called or not, for all the string inside double-quote("). Following are sample code lines -

1.->if(val==23){ month_desc = "a sample data"; }
2.->if(val==23){ month_desc = getCode("a sample data"); }
3.->if(val==23){ month_desc = "a " + getCode("sample data"); }
4.->if(val==23){ month_desc = getCode("sample data"); var2="sample data2";}

Now, expression should be such that it will return true during check for 1, 3 and 4, although, for 3 & 4 getCode has been called for part of the String.

View 2 Replies View Related

JDeveloper, Java & XML :: Extract Data From Clob Field Using Regular Expression

Oct 6, 2011

I want to extract the data from the Clob field. I have the following table,create table test123(col1 char(24), col2 clob); And following data,

Insert into test123 (col1,col2) values ('ABCDE','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
<Attribute DataType="Text-40" DisplayName="DropDirectory"
IsNotDeletable="Y" Modifiable="Y" Name="DropDirectory" Value="${Dir1}"/>
<Attribute DataType="Text-40" DisplayName="PrinterAlias"
IsNotDeletable="Y" Modifiable="Y" Name="PrinterAlias" Value="\Printer3Printer4"/>
<Attribute DataType="Text-40" DisplayName="PrintServerHostName"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
<Attribute DataType="Count" DisplayName="PrintServerPort"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>');
[code]....

The data will be available like this only, The same XML tag is used everywhere.

View 1 Replies View Related

Real Application Clusters :: Check Archive Mount Point Size From Sqlplus Prompt?

Feb 15, 2013

I am in need to find archive log mount point space detail usage through sqlplus on multiple instance. Is there any view in oracle which can give me the detail.

I know one method using external table but that is cumbersome for RAC.

can i get some command like this.

select inst_id,mountpoint,space_usage,space_available,total from GV$table --> returns

View 4 Replies View Related

PL/SQL :: Number Not Showing Zero After Decimal

Feb 21, 2013

i have a column

NUMBER(13,4)

when i insert 234.000

in show only 234

where as when i insert 234.12

i shows 234.12

is there any setting by which i can show 234.0000

View 13 Replies View Related

SQL & PL/SQL :: How To Convert Decimal Number To A Character

Aug 14, 2013

I want to convert decimal number 44 to character(" ' " i.e single quotation);

I have already tried to_char function but it's not working.

View 2 Replies View Related

Maximum Of VALUE Field Of Each Record With Different POINT-NUMBER?

Oct 21, 2011

I need to get the maximum value of the VALUE field of each record with different POINTNUMBER, then do an update on the 2nd table.

table 1
UTCTime TIMESTAMP (6)
INTEGER POINTNUMBER
FLOAT VALUE (126)
INTEGER TLQ

table 2 (idem structure)
UTCTime TIMESTAMP (6)
INTEGER POINTNUMBER
FLOAT VALUE (126)
INTEGER TLQ

Where POINTNUMBER is six thousand different values My query only returns me the maximum of a single record:

SELECT * FROM table1 WHERE value = (SELECT MAX (value) FROM table2);

And the update:
UPDATE table2 SET to a.value = (SELECT MAX (b.value) FROM table2 b);

so does on a single record.That needed to maximize each pointnumber different? I can use a cursor to do this easier?

Insertion should first make a one-time, and then updates every 1 hour

View 1 Replies View Related

PL/SQL :: How To Generate Large Random Number Without Decimal Places

Nov 5, 2012

I need to generate random numbers from the range 71 million to 90 million. While generatind the same iam getting the value with decimal places.

How to generate with out decimal places ?

SQL> select ABS(dbms_random.value(71000000,90000000)) from dual;
ABS(DBMS_RANDOM.VALUE(71000000,90000000))
-----------------------------------------
87283730.7

View 6 Replies View Related

PL/SQL :: Error ORA-01795 - Maximum Number Of Expression In A List Is 1000

Jan 30, 2013

we are using database 11g.

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

i am getting one error while selecting the data from table.

SELECT interco_type, entity, chapter_entity, tran_currency, source_id_entity,
tran_id_entity, mirror_id, gaap_type, counterpart
FROM t_gri_reconid_dtl_agg_gcr;
or
select * from t_gri_reconid_dtl_agg_gcr where rownum = 1 ;

Error:- ORA-01795: maximum number of expression in a list is 1000 ( error is same in both the case )

View 13 Replies View Related

Reports & Discoverer :: ORDER BY Item Must Be Number Of A SELECT-list Expression

Sep 18, 2009

When i'm sorting on a calculated field i'm getting the following error:

ORDER BY item must be the number of a SELECT-list expression

View 3 Replies View Related

SQL & PL/SQL :: Date Check On A Number Column

May 16, 2011

I have a column defined as Number( 8 ) which is supposed to have date values. I would like to check if all the rows in that table have valid dates. We could use to_date(coulmn_name, 'YYYYMMDD') and catch the rownums for error conditions using pl/sql. I would like to know if we could just do it using sql only and return the row numbers for those that are invalid dates?

View 5 Replies View Related

SQL & PL/SQL :: Check Constraint To Disallow Number In Varchar2 Column

Apr 21, 2010

I have table customer which contains a column CUSTOMER_FIRST_NAME

CUSTOMER_FIRST_NAME VARCHAR2(50)

What will be sql statement to add a constraint on the CUSTOMER_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers ?

View 34 Replies View Related

SQL & PL/SQL :: Expression Must Have Same Datatype As Corresponding Expression

Dec 22, 2010

ved>create table test900 ( a number, b number);

Table created.

ved>insert into test900 values( 9,'');

1 row created.

ved>insert into test900 values( 10,null );

1 row created.

ved>select * from test900;

A B
---------- ----------
9
10
ved>select nvl(length(b),0) from test900;
[code]....

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression..Why the above sql ( case 2 ) gives error?

View 9 Replies View Related

Forms :: Put One Check Box To Check All The Check Boxes?

Jul 30, 2011

I want to put one check box to check all the check boxes.how can i do this?

View 5 Replies View Related

PL/SQL :: Function That Returns The Check Digit Of 9-digit Number

Jan 17, 2013

I would like a pl/sql function that returns the check digit of a 9-digit number?

Look at

[UR]......

For example number 109409714 (9 digits) should do check digit. I want validation of check digit, & want to return the check digit.

View 8 Replies View Related

SQL & PL/SQL :: Integer As Decimal

Feb 18, 2010

I have a field in a table that is declared in the CREATE statement as an INT datatype. However, when I query that table using vb.net, the value comes back as a decimal.

How do you declare a field in Oracle as a true integer data type?

View 5 Replies View Related







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