SQL & PL/SQL :: Using Apostrophe / Quotes In A String

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


ADVERTISEMENT

How Many Quotes To Use In Dynamic Query Using IN Clause With String

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

Server Utilities :: Loading String Has Double Quotes In It?

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

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 Replace Apostrophe

Jan 4, 2011

I have a table MOM i.e. Minutes of meeting where important points in a meeting are captured. Now these points may include words like, "don't" or "can't" which will be recorded first in a text file and later copied to the table MOM. Rest of the details are unimportant. All I want to know is how do I enter these words without getting the ORA-01756 error? Do I need to always correct them before entering or is there perhaps another way?

View 2 Replies View Related

Forms :: Language Specific Characters (apostrophe)?

Jun 2, 2010

I have a problem concerning the display of language specific apostrophes (Czech, e.g. in "Další") in Oracle Forms (Labels/Prompts):

In my local Forms Developer /OC4J they are displayed correctly, but when I copy the module onto the IAS (Unix) the are not shown correctly.I already tried several fonts, but none is working for me.

View 2 Replies View Related

SQL & PL/SQL :: Display A Value In Between Single Quotes?

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

SQL & PL/SQL :: Inserting Single Quotes

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

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

SQL & PL/SQL :: Show Value Of Variable In Double Quotes?

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

SQL & PL/SQL :: Concatenate A Comma But Not Single Quotes

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

PL/SQL :: SYSDATE In DDMMYYYY Inside Single Quotes

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

SQL & PL/SQL :: Adding Quotes Around Field Only If Comma Exists Within

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

Server Utilities :: Sql Loader / How To Ignore Error Due To Quotes

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

SQL & PL/SQL :: Double Quotes In Column Name Can Be Replaced With Alternate Character?

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

Server Utilities :: SQL Loader Loads All Fields With Double Quotes Into Staging Table

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

ORA-02085 - Database Link String Connects To String

Jun 19, 2012

I 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 ?

View 7 Replies View Related

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

Mar 14, 2013

I'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

View 3 Replies View Related

PL/SQL :: How To Replace 2 Single Quotes To Single Quote

Nov 21, 2012

I have an script.sql that receives as a parameter an string.

example:

@C:/myscript.sql "o'connor"

user_account_value varchar2(120) := '&1';

EXECUTE IMMEDIATE "Select * from Table where column = :1 "  USING user_account_value I am not sure how to deal with string that contains single quotes.

If the parameter were passed as : "o''connor" this will work
If the parameter is pass as: "o'connor" this will not work.

so my question is what options do I have to deal with dynamic queries and single quotes.

I tried replacing replace(myParameter,'''',''''''); but not working well.

View 11 Replies View Related

How To Use String Buffer Instead Of String Query

May 9, 2008

show an ex to use string buffer for select statemnt

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

String To Array

May 22, 2009

I have a comma seperated string say (tr,er,pr) and i have to convert it to ('tr','er','pr'). if there is function coded to do so.

View 1 Replies View Related

String Of Numbers

Oct 12, 2007

I have the following set of numbers that i am passing in as one input into a stored procedure.

234,456,234,456,567

Now i want to take this list of numbers and use it in an IN statement:

select * from table where column_a in (P_INPUT);

however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!

View 6 Replies View Related

Count For A Particular String?

Oct 9, 2008

say there is astring "mumbojumbo "i need the count of given string in it

ex:when o is given count shuld be 2 when m is given count shuld be 3

is there any pre defined function for counting a given string ...

View 2 Replies View Related

SQL & PL/SQL :: How To Get First Word From String

Aug 22, 2013

how to get first word from string.example i have string like:-

Jack Bore American
Mark D'suz Australian
Raj
Deniel indian
Some

i am expecting first word as out put like :-

jack
mark
Raj
Deniel
Some

in the same maner if i want should get two words also.

View 36 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 :: String Together All Term IDs For ATM

Aug 30, 2010

I want to create a strung together list of ATM IDs for each ATM Location (as one ATM Location(City) can have many ATMs(term ids) this is to allow transaction facts to be not broken down on several lines depending on how many term ids there are for that ATM Location (whenever a new ATM is set-up, a new row is created in the ATM table).

I know I can string it together using a function but I do not have rights to do it so I created SQL in which I feed in the ATM Location as a parameter. I want to do this for ALL ATMs but that is taking forever - is there any way to optimize the below code.

Select max(term_id),atm_location from (Select
(SYS_CONNECT_BY_PATH(TERM_ID,' ' ) ) term_id,atm_location
from
(select term_id , atm_location
from ATM_TABLE
order by term_id asc
)
Start with TERM_ID IN (Select max(Term_ID) from ATM_TABLE group by
ATM_LOCATION )

View 8 Replies View Related

SQL & PL/SQL :: How To Add Some Records Between String

Apr 6, 2012

i have a one table with name as sms_tbl having one field name as sms_text and it contains text messages like

sample_text:Welcome to I-Care, your TPA for your<Insurer> health policy Your ID no is <I-Care ID>

in above text i need to insert records from two tables in the place of <Insurer> and <I-Care ID>.

where <Insurer> and <I-Care ID> records are in two different tables

where i have mapping for this records and there is no mapping for sms_tbl and how to insert these two records in above sample_text

View 3 Replies View Related

SQL & PL/SQL :: How To Split A String

Dec 21, 2011

I have strings like

1) ICE_10001 ICE_10002 ICE_10003

2) ICE_10005 ICE_10006

i want to split above strings like

1)ICE_10001
2)ICE_10002
3)ICE_10003
4)ICE_10005
5)ICE_10006

If it is possible in oracle sql

View 15 Replies View Related

SQL & PL/SQL :: String To Date

Oct 31, 2013

I want to convert the follow string to date: 2013-12-04 11:35:54.89

View 7 Replies View Related







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