find and replace a line in a file

Shell Programming and Scripting
1

Hi,
I am want find and replace in following content in the file.

i want to repalce a word
TABLESPACE XCRM_<ANY_CHAR>
to TABLESPACE XCRM

Sample File to Replace :

LOB(COMPLEX_VALUE) STORE AS
(
    TABLESPACE XCRM_MED_D_NEW
    STORAGE(INITIAL 64K BUFFER_POOL DEFAULT)
    ENABLE STORAGE IN ROW
    NOCACHE
    LOGGING
    CHUNK 8192
    RETENTION
)
TABLESPACE XCRM_MED_D_NEW
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
NOROWDEPENDENCIES
PARTITION BY RANGE(VALUE_DATE)
(PARTITION MDL_PRM_20100924 VALUES LESS THAN (TO_DATE(' 2010-09-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE XCRM_MDL_PRM_T_P20100924
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    STORAGE(INITIAL 64K)
    LOGGING,
PARTITION MDL_PRM_20100925 VALUES LESS THAN (TO_DATE(' 2010-09-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE XCRM_MDL_PRM_T_P20100925
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255

Output :

LOB(COMPLEX_VALUE) STORE AS
(
    TABLESPACE XCRM
    STORAGE(INITIAL 64K BUFFER_POOL DEFAULT)
    ENABLE STORAGE IN ROW
    NOCACHE
    LOGGING
    CHUNK 8192
    RETENTION
)
TABLESPACE XCRM
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
NOROWDEPENDENCIES
PARTITION BY RANGE(VALUE_DATE)
(PARTITION MDL_PRM_20100924 VALUES LESS THAN (TO_DATE(' 2010-09-25  00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE XCRM
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    STORAGE(INITIAL 64K)
    LOGGING,
PARTITION MDL_PRM_20100925 VALUES LESS THAN (TO_DATE(' 2010-09-26  00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE XCRM
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
2 
sed 's/\(TABLESPACE XCRM\).*/\1/' input.txt
1 Like
3

The OP's requirement was to replace

TABLESPACE XCRM_<ANY_CHAR>

so it should be:

sed 's/\(TABLESPACE XCRM\)_.*/\1/' input.txt

Just in case there are some lines with

TABLESPACE XCRMsomeotherstuff

which the OP wants to keep untouched.
Also, if there may be a case with more than one occurrence of this string in one line, add a 'g' for global replacement:

sed 's/\(TABLESPACE XCRM\)_.*/\1/g'
1 Like
4

The global flag would not help you there because the _.* makes sure that everything after and including the underscore gets wiped out... In that case you would need to do something like this:

sed 's/\(TABLESPACE XCRM\)_[^[:space:]]*/\1/g'

An alternative if TABLESPACE XCRM_.*is alway occuring once and by itself on a separate line would be:

sed '/TABLESPACE XCRM/s/_.*//'
2 Likes