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
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?
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
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.
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
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?
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.
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
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).
'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 -
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
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.
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?
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
