PL/SQL :: Embedding Quotes In Strings?
Aug 16, 2013
In the code segment below (hope it appears right) I can understand the use of single quotes in the first two examples but in the third example below I had to use double quotes around the word - Today's - and I not sure I understand why?! I'm aware of the rules ...If you want a single quote to appear in the middle of a string add another single quote to it.If you want a single quote to appear at the beginning or end of a string add 2 single quotes to it.If you want a single quote to appear on its own add 3 single quotes to it.
SQL> select 'This isn''t' from dual;
'THISISN''
----------
This isn't
SQL> select to_number('34@456#789', '999G999D999', 'nls_numeric_characters=''#@'' ') from dual;
TO_NUMBER('34@456#789','999G999D999','NLS_NUMERIC_CHARACTERS=''#@''')
---------------------------------------------------------------------
34456.789
SQL> select to_char(sysdate, 'fm"Today''s" ddth Month YYYY') from dual;
TO_CHAR(SYSDATE,'FM"TODAY''S"DDTHMONTHYYYY')
------------------------------------------------------
Today's 16th August 2013
View 12 Replies
ADVERTISEMENT
Dec 1, 2007
i want to input data from thumb scanner in my form. how to use it ?
View 6 Replies
View Related
Nov 18, 2011
how to write a select query to display a number column value in between single quotes. Ex:i have a emp table in that sal column is a number type.
Ex:
select sal from emp;
sal
----
1234
231
3456
3211
23445I want to display the same above result in b/w single quotes.
ex:
sal
----
'1234'
'231'
'3456'
'3211'
'23445'
for this i need to write a query,
View 5 Replies
View Related
Feb 9, 2011
Here is a sample function which takes in a string of CSV fields and prints the third field :
create or replace function restr(istr varchar2) return INTEGER
is
var_1 varchar2(30);
begin
dbms_output.put_line('input:'||istr);
select regexp_substr(',' || istr,1,3,null,1) into var_1 from dual;
[Code]..
When I pass an input string as :
JOHN,MARY,O'DONNEL,O'CONNELLY,MARK
with quotes/apostrophe in it to the function then it prints the input string in the first dbms_output but errors out at the select with ORA-01760.
how we can use the quote/apostrophe character here ?
View 6 Replies
View Related
Dec 4, 2012
I have a string: 'VOLT,AGE'..
How can I convert this string to: 'VOLT','AGE' using REGEXP_REPLACE...
I am having trouble escaping the single quotes in my query
View 6 Replies
View Related
Dec 4, 2011
declare
v_a varchar2(2000) := 'abcd';
v_e varchar2(2000) := '6666';
v_d varchar2(2000) := 'example';
v_final varchar2(4000);
begin
v_final := '"v_a"'||'''|'''||'"v_e"'||'''|'''||'"v_d"';
-- v_final := '"v_a"';
dbms_output.put_line('v_final: '||v_final);
end;
/
above gives me :
v_final: "v_a"'|'"v_e"'|'"v_d"
so it is printing the variable names, But I want to see values, like this: "abcd"|"6666"|"example"
View 6 Replies
View Related
Oct 8, 2010
In table_A , the primary key is Col_A which is of data type number.I want to concatenate it with ' '
Col_A
______
123
124
select '|| col_A ||'||','
from
Table_A;
The output should be
'123',
'124'
I can concatenate a comma but not single quotes.
View 3 Replies
View Related
Nov 23, 2011
I have a dynamic query which has this clause in it: WHERE [COLUMN NAME] IN (' || theString || ')
My problem is that theString is being passed in through a C# call and the variable is a bunch of strings concatenated together and separated by a comma. Ex: theString = "'val1','val2'"
How many quotes are supposed to go around val1 and val2?
I've tried the following and none work:
'val1','val2'
''val1','val2''
''val1'',''val2''
'''val1'',''val2'''
''''val1'',''val2''''
When I run the procedure in Oracle it works with '''val1'',''val2'''
View 1 Replies
View Related
Dec 4, 2012
I have a requirement like:
Create Table A
( a number,
CreationDate DATE)
PARTITION BY RANGE (CreationDate)
(
Partition p_03122012 VALUES LESS THAN (TIMESTAMP' 2012-12-04 00:00:00'),
[Code]...
NOTE: Partition are named as p_ddmmyyyy where ddmmyyyy is date.
select * from dba_tab_partitions where table_name = 'A'
Now my requirement is :
select * from dba_tab_partitions where table_name = 'A' and partition_name > 'p_ddmmyyyy' (Here I want ddmmyyyy to be from sysdate i.e. date we get from - Select to_char(sysdate,'ddmmyyyy') from dual
i.e. for today it becomes
select * from dba_tab_partitions where table_name = 'A' and partition_name > 'P_04122012'
So it returns me two rows.
How can I do it in single quotes.
View 6 Replies
View Related
Mar 25, 2010
I searched the forum but could not find an answer. I am creating an extract via SPOOL that will send the output to a CSV file. I am comfortable concatenating commas in between the fields, the problem lies in text fields such as last_name which may contain a comma. The requirements of my client state to put double quotes around text fields ONLY if they contain a comma (so as not to throw off the CSV file obviously).
The only thing I could think of was a nested DECODE checked every character of every text field for a , and if it finds one to put " and if not to put nothing. This would get very tedious to program it as there are many text fields and they can be very long. I may even run out of nested DECODE statements (I forget what the limit is now).
View 9 Replies
View Related
Aug 22, 2013
I am loading .csv file into Oracle using sql loader file has strings, and numberics, Strings are surrounded by double quotes(") and field terminated by comma(,)
load data
BADFILE '/var/opt/app/bad/filename'
DISCARDFILE '/var/opt/app/discard/filename'
append into table source_file
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
Some time String fields may have double quotes in it, at that time it is rejecting the records. how to handle those records to load into table.
View 3 Replies
View Related
May 5, 2010
I have control file written like
LOAD DATA
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
but i have data in csv file like
660501,1,"0187591","12"PEGHOOKW/SA",,"04/03/2002",
Since there is an extra double quote (denoting inch) in the third column, im getting an error. Is there any way to avoid this error without modifying the csv file.
View 10 Replies
View Related
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
Feb 14, 2012
Any way to replace the Double Quotes used to enclose column names with an alternative character. This is the SQL I have now that Works!
select (case when CUST is null then "/BIC/Z_SUPPLNT" else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where "/BIC/Z_MAJVEND"=CUST(+) and Material = '1ABCD456'
order by Material
But would like to do something along these lines below but keep getting error "ORA-00936: missing expression".
select (case when CUST is null then chr(34)||/BIC/Z_SUPPLNT||chr(34) else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where chr(34)||/BIC/Z_MAJVEND||chr(34)=CUST(+) and Material = '1ABCD456'
order by Material
View -1 Replies
View Related
Apr 8, 2008
I have requirement wherein i need to compare two strings (with multiple words) and it should return the %(percentage) of comparison.
e.g. "oracle infotech" and "infotech oracle" are 100% match
Do we have any oracle built ins to compare ?
View 6 Replies
View Related
Apr 4, 2012
I have to fetch a string which is between to constant strings in a column.
Ex: Test Column
"The Student Record 10101 is deleted"
"The Student Record 10102 is deleted"
"The Student Record 10103 is deleted"
3 rows.
In this i need to fetch only ID from each row.
create table testtable ( TestCol varchar2(4000));
INSERT INTO TESTTABLE VALUES ('The Student Record 10101 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10102 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10103 is deleted');
View 17 Replies
View Related
Oct 18, 2013
PROCEDURE COLUMN_SPLIT (p_def IN VARCHAR2, p_sch OUT VARCHAR2, p_table OUT VARCHAR2, p_column OUT VARCHAR2)
IS
BEGIN
NULL;
END;
END;
I want to split p_def by dots, check for 3 elements, and return them in p_sch, p_table and p_column for example p_sch will be like hello.howare.you.I want to split it to hellohowareyouI have very limited knowledge with pl/sql.
View 13 Replies
View Related
Jun 10, 2007
The db field is a string-type field that hold strings such as:
'1234'
'753'
'textstring'
'345'
Obviously, if you sort it, it'll be stored as a string such as:
'1234'
'345'
'753'
'textstring'
My client wants it so the numbers sort as integers, followed by string-like strings (sorted alphabetically), so it's like.
'345'
'753'
'1234'
'textstring'
Is there a quick and dirty SQL-only way to doing this in Oracle?
View 3 Replies
View Related
Jul 26, 2007
I have 2 strings that I want to compare for example
string1 = 'ABC~AB/10/1234'
string2 = 'ABC~AB/10/1234~.....'
There could be anything after the 2nd ~ in string 2 is there a easy way of trimming string2 to the first 14 Characters? Or do I have to find the 2nd instance of ~ and then remove everything after (and including) that?
View 2 Replies
View Related
Jan 25, 2012
Im trying to extract, *THIS IS MY STRING* from *<YUVRAJ THIS IS MY SRTING YUVRAJ>* .
In this <YUVRAJ and YUVRAJ> is constant, need to remove which is being appended at begin and end for a set of strings.
View 3 Replies
View Related
Jun 20, 2012
extract the value of the strings for REQUEST_GROUP_CODE and REQUEST_GROUP_APPL_SHORT_NAME. As you can see I have to deal with whitespace differences, case differences etc. I need the values between the quotes for each one. get ACCOUNTING and AR for the first example. I am using db version 11g r1.
WITH TEST AS
(SELECT 'REQUEST_GROUP_CODE="ACCOUNTING" REQUEST_GROUP_APPL_SHORT_NAME="AR" TITLE="AR:AR_SRS_TITLE_ACC_RPTS"' str FROM dual
UNION ALL
SELECT 'REQUEST_GROUP_CODE = "PRINT_CONSINV"REQUEST_GROUP_APPL_SHORT_NAME = "AR"TITLE =
[code]...
View 5 Replies
View Related
Aug 25, 2010
I am having a similar problem like above ONLY in UNIX box where my datafile is delimited by "|". The last field is ITM_CMNT declared as VARCHAR2(60) in Oracle. When I have exactly 60bytes in the last field it rejects the record saying actual 61 and max allowed is 60. If i reduce it to < 60bytes then it is stored as a value enclosed with double quotes. The enclosing double quote is on the next line.
"PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BA"
Expected: the one below is exactly 60bytes.
PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BAT
LOAD DATA
INFILE *
INTO TABLE TMPTLI_LAWSON_ITM_MST
TRUNCATE
FIELDS TERMINATED BY "|"
(ITM_NO, HAZ_MAT_CD, ITM_SHRT_DS, ITM_SON "TRIM(:ITM_SON)", ADDED_DT DATE "YYYY-MM-DD",
AVL_CD , ITM_CST_AMT, ITM_SLL_AMT,
EXCHG_PRC_AMT , ITM_UOM "TRIM(:ITM_UOM)",
PCK_QTY INTEGER EXTERNAL, SPC_HNDL_CD "TRIM(:SPC_HNDL_CD)", EFF_DT DATE "YYYY-MM-DD",
ITM_CMNT "TRIM(:ITM_CMNT)")
View 4 Replies
View Related
May 27, 2011
I have to compare a string entered in a form with a series of English strings in back-end PL/SQL( using LIKE operator )
But it so happens, the string entered in the form is specific to the language used in the country. for ex. in Dutch, it is entered in Dutch language.
So on comparing, it fails as the PL/SQL compares it with English Strings.
View 4 Replies
View Related
Feb 18, 2013
I need to split the given string into muliple sub strings based on one special character
Ex : Speace is Special character
with data as (
select 'ab cd ef gh ' from dual )
select * from data
Required Output :
ab
cd
ef
gh
View 3 Replies
View Related
Mar 3, 2003
Outside of convoluted loop using the SUBSTR() function, is there an easy way to extract each element from a comma-sepearted list that's passed in to a stored proc?
View 4 Replies
View Related
Sep 23, 2011
I am importing some data using an external table, but the file on which the external table is built has some rows where a certain column is populated with two empty space characters.
CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/...';
CREATE TABLE ET_RPDMMA1_PEDI_MSTR (
GCN_SEQNO NUMBER(6),
PDM_MNAGE NUMBER(4),
PDM_MXAGE NUMBER(4),
PDM_MND NUMBER(18,6),
[code].......
This is an except of what's in the external text file. The full text file has been attached.
000011|0030|....|000000.000000| |000000.000000| |0002
000011|0365|....|000000.000000| |000000.000000| |0002
000011|0730|....|000000.000000| |000000.000000| |0002
^
blank spaces may be |
causing error----------
Here is the error message I am receiving. I believe this is caused by the blank fields in the data.
INSERT INTO RPDMMA1_PEDI_MSTR (GCN_SEQNO,....
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
This is difficult to work with because the external table function does not appear to be even reading the file so it's not like I can convert the data as I'm loading into the internal database table. What are some approaches I can use to get Oracle to accept these blank columns and either populate them with blank spaces or set them to null?
View 12 Replies
View Related
Dec 13, 2012
tyring to insert an insery query having string as a column where i am supposed to insert a single quote casuing the problem.
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
getting missing comma with the above query.when i tried to give as
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
insert is happening but saving as "select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij"
how to avoid this and get the select query to store as
select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij
View 5 Replies
View Related
Aug 31, 2010
i have a column 'name' in which value is 'Shailesh Negi',i have to insert 'shailesh' into'first name' column and 'Negi' into 'last name' column respectively.
View 20 Replies
View Related
Jun 8, 2010
There are two tables:
create table songs(song_name text, song_artist text,song_url text, song_cat text, last_edit text);
create table categories(cat_name text, cat_total int);
im trying to create a trigger that, when i insert a new song in the songs table, it will check the category of the song (song_cat) and increase the respective cat_total (from table categories) by 1.
here is what i've done so far:
drop trigger countcat;
CREATE TRIGGER countcat AFTER INSERT ON songs FOR EACH ROW
update categories SET cat_total= cat_total +1
WHERE cat_name = (select song_cat FROM inserted);
What to write in the cat_name = (select ...). I have tried lots of stuff but still nothing. when i use this, i get the error that mydatabase.inserted doesnt exist
View 4 Replies
View Related
Apr 5, 2011
I have a function in PL/SQL that uses CTE to obtain several strings. I need to return those strings to C#, either as a Return Value or a OUTPUT parameter.
I've managed to do that using PLSQL Associative Array, but it has the unnecessary usage of Array Bind Size, and I do not need to know what will be my Array size. Is that any other way to do it?
My Package is the next one:
create or replace
PACKAGE Pkg1 IS
TYPE listResults is TABLE of VARCHAR2(100) INDEX BY BINARY_INTEGER;
PROCEDURE CalculateResults ( iCode IN VARCHAR2, iAg IN VARCHAR2, resultados OUT listaResultados );
END Pkg1;
create or replace
PACKAGE BODY Pkg1 AS
PROCEDURE CalculateResults(
iCode IN VARCHAR2,
[code]......
View 4 Replies
View Related
