CODEWITHSUNDEEP
mysqlsql
EternalSunShine
EternalSunShine

Reputation: 409

Mysql Windows path update

I am facing issue while updating the substring of paths stored in table . I need to change sub string C:\Program Files (x86) as %APPDATA% for column path.

Below is SQL and this SQL is not updating the path, showing 0 updated rows. What am i doing wrong here?

/*Path: C:\Program Files (x86)\Fiddler2\Fiddler.exe*/                     

UPDATE software
SET path = REPLACE(path, 'C:\Program Files (x86)', '%APPDATA%')
WHERE path LIKE '%C:\Program Files (x86)%';

Upvotes: 1

Views: 660

Answers (1)

spencer7593
spencer7593

Reputation: 108450

In MySQL string literal, the backslash character needs to be escaped, by preceding it with another backslash.

As a simple demonstration... 

SELECT 'C:\foo'   AS one_backslash
     , 'C:\\foo'  AS two_backslash

Suggestion:

Test expressions with a SELECT statement before running an UPDATE 

SELECT REPLACE(path, 'C:\\Program Files (x86)', '%APPDATA%') AS new_path
  FROM software s 
 WHERE path LIKE '%C:\\Program Files (x86)%'

Upvotes: 3

Related Questions