SQL & PL/SQL :: How To Get Last 4 Characters In A String
Dec 12, 2010how 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.
ADVERTISEMENT
SQL & PL/SQL :: Replace Characters In A String?
Apr 7, 2011replace 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.
How To Count Distinct Characters From A String
Sep 26, 2005how to count different characters from a string ....
View 7 Replies View RelatedSQL & PL/SQL :: Removal Of Special Characters In A String
Jan 2, 2013I 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.
SQL & PL/SQL :: Count Repeated Characters In String
Mar 9, 2011Is 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
SQL & PL/SQL :: Remove Special Characters From Input String?
Aug 5, 2010I 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?
SQL & PL/SQL :: How To Find Extended ASCII Characters From A String
Sep 19, 2013I 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.
SQL & PL/SQL :: Query To Get Whether A String Contains 2 Upper Characters And Numbers Using Regular_exp
Jun 12, 2013A 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 RelatedPL/SQL :: Translate Function Remove Alpha Characters From String
Feb 7, 2013I 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
SQL & PL/SQL :: How To Extract First 3 Characters From Each Word In A String / Sentence And Separate With Underscore
Oct 22, 2013I 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.
SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers
Jul 13, 2011I 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
SQL & PL/SQL :: Removing Special Characters And Get Desired Characters From Column Values
Jul 23, 2013create table test
(
name varchar2(50),
descd varchar2(50)
)
insert into test values ('kethlin','da,dad!tyerx');
insert into test values ('tauwatson','#$dfegr');
insert into test values ('jennybrown','fsa!!trtw$ fda');
insert into test values ('tauwatson','#$dfegr ,try');
how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?
so my o/p be like;
Quote:('ketlin','dadadtyerx')
('tauson','dfegr')
('jenown','fsatrtw fda')
('tauson','dfegr try')
ORA-02085 - Database Link String Connects To String
Jun 19, 2012I have this error (and solution):
ORA-02085: database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?
SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String
Mar 14, 2013I'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
How To Use String Buffer Instead Of String Query
May 9, 2008show an ex to use string buffer for select statemnt
View 1 Replies View RelatedSQL & PL/SQL :: Characters Format On The Web?
Jul 10, 2011We have a production database that have : NLS_LANGUAGE=FRENCH_FRANCE.WE8ISO8859P1.
We use (INSERT, UPDATE) arabic and french languages, and it works properly.
When I issue SQL statments to retrieve arabic data (with SQL*PLUS), it works and it returns correct arabic format.
When I use PHP, with the same small query, the arabic format is not correct.
I've tried changing the encoding characters on my browsers (IE and FF) and it's still incorrect.
NLS-NUMERIC CHARACTERS Changes?
Jun 20, 2011I have set NLS_NUMERIC_CHARACTERS to ',.' but somehow during my java application life-cycle it got changed to '.,'! Is there any way to find what causes this? I can't find what or who change it. I have ordinary Java app which connects to Oracle 11.2.0.1.0 DB and as far as I know NLS_NUMERIC_CHARACTERS is not set explicitly or any other NLS_XXX setting. Is there any way to look in some logs for this?
View 1 Replies View RelatedJapanese Characters In Oracle
Jun 22, 2013I have a XML file which contains Japanese characters and it is parsed by a UNIX script and nawk utility and writes the data to a flat file separated by delimiter. I could the Japanese characters proper in the flat file.
I use SQLoader (within a unix script) to import this data from the flat file to Oracle database. If I view the data in the database through Toad, the Japanese characters are showing differently (not as in XML or in flat file).
But if do a export for the particular table to a flat file through Toad, I can see the Japanese characters proper in the exported flat file.
(Note : I have set the env variables NLS_LANG=Japanese_Japan.JA16SJIS, LC_CTYPE="en_CA.UTF-8" in both XML parser and the loader script)
Why I couln't see the Japanese characters while viewing through Toad.
10g SQLLoader With Multibyte Characters
May 11, 2011Here's an odd problem. I'm trying to load German characters positionally (not CSV) using Linux 10g. I don't get this error on Windows or via CSV, but I'm bound to the method and platform.
The problem is simplified thus. I have 2 columns, the 1st varchar2(8) and the 2nd a numeric(3). The error I'm getting is Invalid number only on rows with special characters. Let me demonstrate.
The file has been loaded into Linux and corrected using iconv.
[oracle@basic sqlldr]$ cat jh.txt
ELEKTROM001
ZEIPR�SI002
This is the loader control file
[oracle@basic sqlldr]$ cat jh.ctl
load data
characterset utf8
infile 'jh.txt'
replace into table TEMP1
(
FLD1 POSITION(1:8) CHAR,
FLD2 POSITION(9:11)
)
The 1st rows is accepted, but the second fails in sqlldr with
Record 2: Rejected - Error on table TEMP1, column FLD2.
ORA-01722: invalid number
The logical assumption is that the double width character is not being properly read by sqlldr but I can find no advice on other setting.
My nls parameters look like this.
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE ENGLISH
NLS_TERRITORY UNITED KINGDOM
NLS_CURRENCY #
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE ENGLISH
NLS_CHARACTERSET UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH24.MI.SSXFF
PARAMETER VALUE
------------------------------ ------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY ?
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
I've tried using other sqlldr options such as LENGTH SYMANTICS and BYTEORDER but with no success.
Dbms_xmlgen Tag Trunk After 80 Characters?
Mar 1, 2011Try to send a query to a remote sql server (Oracle DB I guess). I found dbms_xmlgen which generate xml output, very useful, but sometimes lines are trunk and xml tag as well.
-------------------------------------------
query
-------------------------------------------
set pages 0
set linesize 150
set long 9999999
set head off
select dbms_xmlgen.getxml
('select * from (select no,subject where...)') xml from dual ;
quit
-------------------------------------------
extract of the output
-------------------------------------------
[...]
<ROW>
<NO>10260253</NO>
<SUBJECT>123456789 123456789 123456789 123456789 123456789 123456789 12</SUBJE
CT>
</ROW>
[...]
-------------------------------------------
issue
-------------------------------------------
</SUBJECT> TAG is trunk and makes xml invalid for python parsing
SQL & PL/SQL :: Restricting Other Language Characters?
Jan 24, 2011I need to give validation for not allowing french or arabic or hindi alphabets or numbers except for only english alphanumeric letters.
I understood how to restrict special characters or spaces .
regexp_instr(i, '[^[:alnum:]]') = 0
This function allow only characters and numbers in English language and doesnt allow special characters.
I need to restrict further by not allowing any other language characters or numbers except only english alphanumeric letters.
SQL & PL/SQL :: Filter Special Characters?
Jan 8, 2013I have one column name party_name containing Korean Characters and English characters.Some of the English characters have different symbols.My requirement is to get the data and exclude those symbols but not Korean characters.
Already I used a function to replace special symbols with space.The function contains code based on ASCII values it works good but it filters Korean characters too.the attachment of the screenshot, When I double click the name it shows with some question mark.
SQL & PL/SQL :: How Special Characters Got Inserted Into DB
Jul 9, 2013We are using Release 11.2.0.3.0 of Oracle. I am having below special characters inserted into one of my columns, how this value got inserted, (what is the source) i need to track it down.
We dont have any audit trigger on this table to track one level below. As per JAVA guys this is uploaded through a file and the file is having well defined characters and no special characters for this column value also they uploaded the file again but its now going fine with no such special characters. So they put it on DBA's to find how special characters came into database?
Again the editor is not recognising all the characters , so i got the ASCII value for each of the characters in the string, its as below.
String - ‡Mw‹O--ggÑ ÆÔéÓÞ³ µmT¤O ˆÓ`ôiyïÎ!Ž
ASCII character is : ‡ ASCII Value Is : 14844065
ASCII character is : ‹ ASCII Value Is : 14844089
ASCII character is : -- ASCII Value Is : 14844052
ASCII character is : ASCII Value Is : 49793
ASCII character is : Ñ ASCII Value Is : 50065
[code]....
SQL & PL/SQL :: Exact Count Of Characters
Jan 7, 2013How can I find out that exact count of '~'?
SELECT NVL(LENGTH('~~~~~~~~~~~~~~~~~')-LENGTH(REPLACE('~~~~~~~~~~~~~~~~~','~','')),0) result
FROM dual;
SQL & PL/SQL :: How To Remove Special Characters
Mar 22, 2013I 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
Forms :: How To Set A Format Of Characters
May 30, 2013i have to enter pan_no through my form into database, and pan_no format is like BWHPK2334M as first 5 is alphabets then 4 letters and last one is alphabet, how to validate it in my form. can i do this by set fomat mask in property palette and if yes then how, oterwise the 2nd option may be is trigger when validate item, but with which format i should match the entered data.
View 5 Replies View RelatedForms :: Arabic Characters?
Mar 17, 2010problem is that when i call run_product to generate a report from a form i have the name of employee in arabic characters appear in wrong form. yet when i use query from forms directly or from reports directly. name appears correct.
i want the arabic charcters to appear correct when i call to show report from a form..
SQL & PL/SQL :: Replacing Multiple Characters?
May 21, 2013I am doing some ETL that I need to run "faster". The function in which I am interested removes low ascii code characters from a string. Please see the timing below and the definitions of the of the functions below those. I am selecting just the first 100K rows for testing and timing purposes only. In production, we are doing millions of records several times a day, thus the desire for "faster". Selecting with no functions is very fast, 0.2 seconds. We would really really want to convert at least 100K rows per second.
The best I can do is get it down to around five seconds using clear_nonlegal. That is, ironically, the one that I thought would be the slowest. It's making thirty-one calls to REPLACE. I would have guessed that the other two would be much faster. I am guessing that REPLACE is just much better optimized than TRANSLATE and, of course, my homegrown PL/SQL, which isn't optimized at all.
So, my question is thisif there is a way I can optimize my custom function, or maybe know of a better already optimized standard SQL and/or Oracle function that would do the job? I am thinking about trying to use a Java stored procedure, but I have never done that before, I am not currently set up for it, if it would be any faster anyway. Is Java faster with string manipulation the PL/SQL? I am thinking it would be really fast to call a C method,
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aggs@AGGSTEST
SQL> set timing on
SQL> SELECT COUNT(*)
2 FROM (SELECT DISTINCT keyword_dest_url
3 FROM se_keywords sek
[code]...
SQL & PL/SQL :: Restricting Special Characters?
Apr 2, 2010I have the data in the table like
Name
----------
a/bc
a*bc
a_bc
a&bc
So while retreiving the data from the above table so i need the data like
Name
--------
abc
abc
abc
abc
I need to Restrict the Special Characters.
SQL & PL/SQL :: Detecting Hidden Characters
Jul 31, 2011I am not sure if the problem is related to hidden characters but its my best guess so far. I am trying to enhance a part of the ERD by creating a lookup for a column one of the table that uses text (finite set of values).
CREATE TABLE N_AGREEMENT_STATUS
(
STATUS_ID NUMBER(2) PRIMARY KEY,
STATUS_NAME VARCHAR2(10 BYTE)
);
INSERT ALL
INTO N_AGREEMENT_STATUS VALUES (1, 'INACTIVE')
INTO N_AGREEMENT_STATUS VALUES (2, 'ACTIVE')
INTO N_AGREEMENT_STATUS VALUES (3, 'CLOSED')
INTO N_AGREEMENT_STATUS VALUES (4, 'CANCELLED')
SELECT * FROM DUAL;
when I try to update the source table no update takes place (0 records updated) if I used the following statement:
ALTER TABLE N_AGREEMENT ADD STATUS_ID NUMBER(2);
UPDATE N_AGREEMENT SET STATUS_ID =
(
SELECT STATUS_ID
FROM N_AGREEMENT_STATUS
WHERE N_AGREEMENT.STATUS = STATUS_NAME );
but it works fine only if I used:
UPDATE N_AGREEMENT SET STATUS_ID =
(
SELECT STATUS_ID
FROM N_AGREEMENT_STATUS
WHERE N_AGREEMENT.STATUS LIKE STATUS_NAME || '%'
);
The strange thing is that when I use:
SELECT N_AGREEMENT.STATUS, N_AGREEMENT.STATUS_ID
FROM N_AGREEMENT
WHERE N_AGREEMENT.STATUS = 'ACTIVE';
it returns correct results and all status = 'ACTIVE' appear correctly!