CODEWITHSUNDEEP
mysqlreplace
Cedric
Cedric

Reputation: 13

Replace part of a column value in MySQL Select query

I have read the forums to find a solution for my issue but I am stuck with a MySQL error when I use the query. I want to extract part of a field, everything that is after \\nt4\applications\prod\hde\atn\ in the FILE_NAME column Here is the query:

SELECT FILE_NAME, 
REPLACE (FILE_NAME,'\\nt4\applications\prod\hde\atn\','') as newfilename 
from atn_documents

It always return me a

syntax error near ''\

It looks like the string to look into can not contains \ character??

Can anyone drive me?

Thanks Cedric

Upvotes: 1

Views: 29

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Use SUBSTRING_INDEX:

SELECT
    SUBSTRING_INDEX(FILE_NAME,
                    '\\nt4\\applications\\prod\\hde\\atn\\',
                    -1) AS path
FROM yourTable;

Demo

The above query is a verbatim implementation of your requirement, since it returns only what is after the path of interest. Also note that the immediate reason why your query does not even run is that you need to escape backslashes by doubling them up \\ if you want them as literals.

Upvotes: 2

Nishant
Nishant

Reputation: 157

You have to escape the "\" character in the query. You can add additional "\" to escape it.

e.g. 

SELECT FILE_NAME, REPLACE (FILE_NAME,'\\nt4\\applications\\prod\\hde\\atn\\','') as newfilename from atn_documents

Upvotes: 0

Related Questions