There is a problem in writing a query for sql. The essence of the problem is as follows: there is a filled table with a column FILE_NAME, in this column it is necessary to delete the number 0 in some elements in the row. This 0 is always in the same place on the line. Below I attach a screenshot with the data, the yellow highlighted characters that need to be deleted. I found the regexp_replace function on the Internet, but I didn’t quite understand how to use it.

problem screenshot

    2 answers 2

    If you understand the selection condition correctly, then: REGEXP_REPLACE (file_name, '0 (? = \ D {3} _ \ d {3} $)', '')

    • When writing the following query for verification: SELECT regexp_replace (FILE_NAME, '0 (? = \ D {3} _ \ d {3} $)', '') as FILE_NAME_CORRECT, FILE_NAME FROM [TABLE] Returns the following exception: Message 195, level 15, state 10, line 1 'regexp_replace' is not a recognized built-in function name. - Anton Ovechkin
    • It was necessary to at least indicate the DBMS, otherwise I am not a psychic, because they themselves indicated that they looked about regexp_replace. And your solution could be much shorter if you used this template. - JavaJunior

    I solved the problem.

    Answer:

    UPDATE [TABLE] <p>SET FILE_NAME = substring(FILE_NAME,0,9) + substring(FILE_NAME, 10,len(FILE_NAME)) <p>WHERE (FILE_NAME LIKE('[0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9]'))