PL/SQL :: Translate Function Remove Alpha Characters From String

Feb 7, 2013

I 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

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Remove Special Characters From Input String?

Aug 5, 2010

I have a following table,

create table test1(col1 varchar2(20));

insert into test1 values('4711-3/01');

I believe we need to use Translate function to get rid of special characters, But I would not be knowing what sort of special charecters which appear in the string, In that case how do I use Translate?

View 29 Replies View Related

SQL & PL/SQL :: Replace Non Alpha Numeric Characters

Jul 1, 2004

I have a function that will replace the contents of the input parameter and replace any non-numeric characters.� I just want to know if there is a more efficient way to code this (oracle 8i or higher).

function strip_non_numeric(p_string in varchar2) return varchar2 is
Result varchar2(100) := '' ;
x_length number;
begin
SELECT LENGTH(p_string) INTO X_LENGTH FROM DUAL;
FOR i in 1..X_LENGTH LOOP

[Code]....

View 6 Replies View Related

PL/SQL :: Difference Between Translate And Replace Function

Jan 6, 2013

Explain with the best example for difference between translate and replace function.

View 2 Replies View Related

Application Express :: 4.2 Translate (Function And Global Variable Declaration) Data?

May 1, 2013

i 'm using APEX 4.2.1.00.08 and i 'm wondering if there is a way to translate "Function and Global Variable Declaration" textarea. Every other script textarea is available for translation, but not this.

I know that this is code is loaded on header but may contains important alert messages of global functions.

View 1 Replies View Related

SQL & PL/SQL :: How To Remove Special Characters

Mar 22, 2013

I need to removed special characters (!, ", #, $, %, &, /, () from a string, i have a table with sll this special characters and words that i have to remove from the string.

How can i do that ?

i have a string with |R!$#&2-_D%2 and i want to get R2-D2

SELECT '|R!$#&2-_D%2' as Original, 'R2-D2' as Correct
FROM DUAL

View 5 Replies View Related

Remove Invalid XML Characters From DM Field

Nov 21, 2006

I am using an Oracle view and package to extract data from my DB to build an XML file on the fly.

My problem is that on very rare occasions, an invalid XML character will be in the database. This, of course, causes my XML file to error.

My question is: What are the possible ways to remove invalid XML characters when selecting out of a DB field? Any function that has been written for this type of thing, or is a VERY long 'translate' more of what I should be doing?

View 1 Replies View Related

SQL & PL/SQL :: Remove String Duplicates

Oct 11, 2013

Removing duplicates from a string that contains years and "-".

Example: 1988-1997-2000-2013-1998-1965-1997-1899

I know this can be done in regular expressions but have no experience in this subject.

select REGEXP_REPLACE(.....) from dual;

View 16 Replies View Related

SQL & PL/SQL :: How To Get Last 4 Characters In A String

Dec 12, 2010

how to get last 4 characters in a string. But i don't know the length , for example the string is

abcdefghij

i want only ghij.

View 5 Replies View Related

SQL & PL/SQL :: To Remove Duplicates From Concatenate String

May 12, 2011

I have a query like

SELECT country_name,
substr(SYS_CONNECT_BY_PATH(product_name,','),2) as PRODUCT_NAME,
substr(SYS_CONNECT_BY_PATH(SPEED_VALUE,','),2) as SPEED_VALUE,
substr(SYS_CONNECT_BY_PATH(i.SUPPLIERNAME_ACCESSPROTYPE,','),2) as SUPPLIERNAME_ACCESSPROTYPE
FROM (SELECT b.country_name,b.product_name,b.speed_value,(supplier_name|| supplier_product || access_product_type)as
[code].......

In the result , I am getting repeated values for product_name and speed value,something like 'ALL Products,All Products,All Products'in the product_name column and '128Kbps,128Kbps'in Speed_vale.i am not able to remove the repeated values here.

View 2 Replies View Related

PL/SQL :: Remove Consecutive Occurrence From String?

Jun 4, 2013

version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

i want to ,remove consecutive occurance from string

Example I/P: 'POWELL POWELL BRIAN K AND BONNIE POWELL JARRELL JARRELL'
to O/P : 'POWELL BRIAN K AND BONNIE POWELL JARRELL'I tried the below code is Working fine , But i wanted to do this using Regexp or Some other Better Method
WITH T

[Code]....

View 8 Replies View Related

SQL & PL/SQL :: Replace Characters In A String?

Apr 7, 2011

replace the first 5 commas with the character '|' in the below string:

'Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver'

I tried:

SELECT regexp_replace('Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver',
',','|',1,5) from dual

but it only replaces the 5th comma.

View 3 Replies View Related

PL/SQL :: String Conversion - Remove Single Codes?

May 22, 2013

I am getting string from my tool like this.... .. ‘ ‘PH1234’,’Ph3456’,’PH5678’ ‘

I wanted to remove single codes and take the each value and I have to process. Need output like this....

PH1234,
Ph3456,
PH5678.

View 4 Replies View Related

How To Count Distinct Characters From A String

Sep 26, 2005

how to count different characters from a string ....

View 7 Replies View Related

SQL & PL/SQL :: Removal Of Special Characters In A String

Jan 2, 2013

I am looking for a SQL query which checks for special character(s) in a string and remove them if any.

for e.g: input string : 'ABCD $ & * # 123 Test'
expected output should be in upper case : 'ABCD123TEST'

we do not know the position of special characters and also what kind of special characters will come as input.

View 17 Replies View Related

SQL & PL/SQL :: Count Repeated Characters In String

Mar 9, 2011

Is there any function to count number of specific character inside string. For example :

my string is 'ABCEF AYZA'

I want to calculate how many 'A' exist in my string. result should be = 3

View 17 Replies View Related

SQL & PL/SQL :: Remove Spaces By Excluding Double Quotes From A String

Oct 29, 2012

I want to remove more than one space from a string by excluding double quotes.

For example:

I/P: Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")

O/P : Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")

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 :: Replace Double Space With Single Space And Also Remove Junk Characters

Jul 1, 2013

I want to replace double space with single space and also remove junk characters from the data. How can I do that?

CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"

View 8 Replies View Related

SQL & PL/SQL :: Remove Last Comma End Of String And Load Clob Data Into Table

Aug 29, 2012

To remove the last comma end of string and load the Clob data into table. create table test(name clob)

View 2 Replies View Related

SQL & PL/SQL :: Query To Get Whether A String Contains 2 Upper Characters And Numbers Using Regular_exp

Jun 12, 2013

A 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 Related

PL/SQL :: Remove Duplicate Values From Concatenated Long String Of State Codes

Dec 4, 2012

Database version: 11.2.0.3.0

I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.

select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;

Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).

View 4 Replies View Related

SQL & PL/SQL :: How To Extract First 3 Characters From Each Word In A String / Sentence And Separate With Underscore

Oct 22, 2013

I have some strings like

'Net Amount Payable by an Individual',
'Net Amount Payable by an Individual+Tax',
'Total Amount Payable towards Service',
'Total Amount Payable towards Service.+Tax'

I need to extract the first three letters from each word and separate them using an underscore. The output should be as follows for the above strings -

Net_Amo_Pay_by_an_Ind
Net_Amo_Pay_by_an_Ind_Tax
Tot_Amo_Pay_tow_Ser
Tot_Amo_Pay_tow_Ser_Tax

I request to let me know the way to get the ouput as mentioned.

View 4 Replies View Related

SQL & PL/SQL :: Function For Allowing Only Numbers And Characters

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

SQL & PL/SQL :: TRIM Function - Remove Symbol Only From LEFT Or RIGHT Side

Feb 22, 2011

Is there any possibilites to remove the symbol '*' only from LEFT or RIGHT side, instead BOTH the side.

select '>' || trim (both '*' from '***removing stars at both sides***') || '<' "Stars removed" from dual;

View 4 Replies View Related

SQL & PL/SQL :: Read International Characters Through Utl_file Function

Jul 19, 2010

i 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 Related

Function To Convert Special Characters To HTML Code?

Nov 5, 2008

The 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

View 3 Replies View Related

SQL & PL/SQL :: Function Code To Restrict User From Entering More Than 30 Characters

May 24, 2011

Need 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 Related

SQL & PL/SQL :: Remove String Before And After Pipeline To Get String Between Pipeline?

May 10, 2010

I have a string like below:

string = 'HEADER||MEAL||15'

How to get 'MEAL' string? The length of the string can be various. Means, 'MEAL' can be 'INFLIGHT'. So, i cant use the substr. Is there a function that can recognize the pipeline? so that i can remove all the string before the pipeline and after the pipeline to get the string between the pipeline?

View 9 Replies View Related

SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers

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







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