How to replace suffix of data?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to replace suffix of data?

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    How to replace suffix of data?

    Hi folks,

    I need to replace the suffix "_TST" with "_TRN1" in a column .
    For example , if the column contains the value xxx_tst I need to replace it to xxx_trn1.
    How to do it?

    Thanks in advance,
    Nir

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    How about something like...
    Code:
    update your_table
    set    your_column = replace(your_column, '_TST', '_TRN1')
    where  your_column like '%_TST'
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi PAVB,

    Thanks for your reply but I needed to replace only the appearances in the end of the word.
    If the string "_TEST" (by mistake I wrote "_TST") appears in the middle of the word ,then the "replace" command will replace all the appearances.
    I've just solved my problem as the following:

    Code:
    update table
    set x=SUBSTR(x,1,length(x)-4)||'TRN1'
    where substr(x,instr(x,'_',-1,1)+1,length(x)-instr(x,'_',-1,1))='TEST';
    Best regards,
    Nir

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    well... no literal on the middle of the string was bound to be changed by my script; look at the where clause, it filters out everything that does not have your desired literal at the end of the string.

    Code:
    where  your_column like '%_TST'
    Either way, I'm glad you solved the problem.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Thanks for your simple "WHERE" clause.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width