I have a variable whose value is found using sql query.

I want to remove the new line charcater from that variable since I want to concatenate this variable with the other. Below is the code:

dt=`sqlplus -s user/pwd@servicename <<EOF
set feedback off;
set head off;
select  replace(to_char((sysdate-7),'YYYYMonDD')||'_'||to_char((sysdate-1),'YYYYMonDD'),chr(10), '') from dual;

echo "test $dt"

If you are using bash, you can use Parameter Expansion:

dt=${dt//$'\n'/} # Remove all newlines.
dt=${dt%$'\n'}   # Remove a trailing newline.

The following should work in /bin/sh as well:

}"                # Remove a trailing newline.
| improve this answer | |
  • 3
    Just FYI, when you're using ${var//a/} you don't need to use last /, ${var//a} will do exactly the same. – rush Nov 29 '12 at 13:28
  • Thank you all.Below thing worked dt=${dt//$'\n'/} # Remove all newlines. – Pavani Nov 29 '12 at 13:58
  • For those curious in the full syntax, here is a link to the bash documentation: gnu.org/software/bash/manual/html_node/… – kalaxy Jun 26 '19 at 17:40

Sounds like you need "tr", something like:

 echo ${dt} | tr -d '\n'

man tr for detail, as usual

| improve this answer | |

This work on Linux (bash):

dt="$(echo "$dt"|tr -d '\n')"

On Linux, or other systems with GNU's date utility, this also works to get that value for dt: (without involving Oracle...)

dt="$(date -d 'yesterday' +%Y%b%d)_$(date -d '7 days ago' +%Y%b%d)"
| improve this answer | |
  • +1 for avoiding sqlplus. If you're on a system without GNU date but you have Tcl: echo 'puts [clock format [clock scan "-1 week"] -format %Y%b%d]_[clock format [clock scan yesterday] -format %Y%b%d]' | tclsh – glenn jackman Nov 29 '12 at 14:55

from the Oracle sqlplus manual

SET PAGES[IZE] {14 | n} Sets the number of lines on each page of output. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

so add a set pagesize 0 to your script to avoid a heading blank line.

for most of my scripts I use the settings in the following code piece of code:

dt=`sqlplus -s user/pwd@servicename < < EOF
set feedback off
set pagesize 0
set trimout on
set trimspool on
set linesize 300
set echo off
set verify off

select  replace(to_char((sysdate-7),'YYYYMonDD')||'_'
||to_char((sysdate-1),'YYYYMonDD'),chr(10), '') from dual;

echo "test $dt$dt"
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.