Full Contact Computing
Bare knuckles, no holds barred computing

SQLPlus Character Work-Arounds

Sometimes you have to do battle with SQL*Plus to get it to do what you want. Here are some airstrikes you can call in if it seems to be winning.

In this blog  https://fccomputing.wordpress.com/2010/03/31/sqlplus-substitution-variables/  I talked about substitution variables. SQL*Plus uses the ampersand (&) as a substitution variable, which can cause some problems if you want to use the ampersand as a character in your data. Suppose you want to insert “a & b” into a table:

SQL> insert into scott.dept values (‘77’,’a & b’,’HERE’);

SQL is going to then prompt me:

Enter value for b:

It sees the ampersand as a variable. If I enter the letter b, then it will accept and insert the row. However, the ampersand will be gone, since it was considered a variable that is now replaced by b:

SQL> select * from scott.dept where deptno = ‘77’;

DEPTNO   DEPTNAME     LOC
————-  ——————  ——–
             77   a b                      HERE

Now any query that relies on the deptname being “a & b” will fail since there is no “&”.

Solution #1 – Change the substitution variable to something else (~ instead of &):

SQL> SET DEFINE ~   
insert into  scott.dept values (‘67’,’a & b’,’THERE’);
1 row created.

SQL> select * from scott.dept where deptno = ‘67’;
DEPTNO   DEPTNAME     LOC
————-  ——————  ——–
             67   a & b                      THERE

Solution #2 – Set the substitution variable to undefined:

SQL> SET DEFINE OFF 
SQL> insert into  scott.dept values (‘57’,’a & b’,’ELSEWHERE’);
1 row created.

SQL> select * from scott.dept where deptno = ‘57’;
DEPTNO   DEPTNAME     LOC
————-  ——————  ——–
             57   a & b                      ELSEWHERE

Solution #3 – Tell SQL to not look for a substitution variable:

SQL> SET SCAN OFF 
SQL> insert into  scott.dept values (‘87’,’a & b’,’NOWHERE’);
1 row created.

SQL> select * from scott.dept where deptno = ‘87’;
DEPTNO   DEPTNAME     LOC
————-  ——————  ——–
             87   a & b                      NOWHERE

Solution #4 – Concatenate the statement to allow the &:

SQL> insert into  scott.dept values (‘89’,’a ‘ ||’&’|| ‘ b’,’EVERYWHERE’);
1 row created.

Note that in the above insert statement, there is a space after the “a” and a space before the “b”. This is important since the result will be “a&b” vs the desired “a & b” if no spaces are within the quotes.

SQL> select * from scott.dept where deptno = ‘89’;
DEPTNO   DEPTNAME     LOC
————-  ——————  ——–
             89   a & b                      EVERYWHERE


Single Quotes

OK, suppose you need to insert a single quote. The insert statement will need extra single quotes depending on where the desired quote mark is in the name. For a quote in the middle (a ‘n b), add in one extra single quote:

SQL> insert into  scott.dept values (‘99’,’a ‘’n b’,’NEAR’);
1 row created.

SQL> select * from scott.dept where deptno = ‘99’;
DEPTNO   DEPTNAME     LOC
————-  ——————  ——–
             99   a ‘n b                      NEAR

If the single quote is at the beginning of the word (‘a) you’ll have to insert two extra single quotes:

SQL> insert into  scott.dept values (‘49’,’’’a’,’FAR’);
1 row created.

SQL> select * from scott.dept where deptno = ‘49’;
DEPTNO   DEPTNAME     LOC
————-  ——————  ——–
             49   ‘a                          FAR

Same thing applies if the quote is at the end (a’) – use ‘a’’’ in your insert statement.
If you want single quotes on both sides of the name (‘a’), use ‘’’a’’’.
Want just a single quote? Use four single quotes in the insert: ‘’’’.
Two single quotes? Use six single quotes in the insert: ‘’’’’’.
Inserting a null value, use just two single quotes: ‘’.
Double quotes are easy, just enclose them in single quotes: ‘”a”’ inserts as “a”.

Bogus Characters

Last tip: Have you ever done a SQL*Loader insert and found that the file was created in Windows and has bogus ASCII characters at the end of every line? They can be eliminated!

The first step is to figure out what the bogus character is. Find an ASCII chart (here’s a Google search for you  http://www.google.com/#hl=en&source=hp&q=ascii+chart&aq=f&aqi=n1g-s1g9&aql=&oq=&gs_rfai=&fp=25bac56246434a91  )

I think this one is the easiest to read:

http://www.columbia.edu/kermit/ascii.html

 So suppose you have ^M (called CTRL-M or Control-M) at the end of every line. These are carriage returns, and the chart listed above helpfully shows:

 Char Dec Col/Row Oct Hex  Name and Description
        13    00/13      15   0D    CR  (Ctrl-M)  CARRIAGE RETURN

The leftmost column tells us that this is “character 13”.  Now we have all we need to build our “character 13”-killing code:

     update owner.table_name
     set column_name=replace(column_name, chr(13), ”);

That’s 2 single quotes at the end. Remember from above that 2 single quotes will insert a null value. So what this command does is replace all of the ^M characters with a null value. Simple, easy, do a commit and you’re done. Now maybe you can figure out what is inserting those bogus characters and make it stop so you don’t have them there in the first place.

Now go forth and bend SQL*Plus to your will!

Advertisements

No Responses to “SQLPlus Character Work-Arounds”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: