Mysql Windows path update

Question

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)%';

this SQL is not updating the path, showing 0 updated rows. While selecting the records, SQL working till '%C:%' string match. When I am adding '%C:/%', no record is selecting. — EternalSunShine
– EternalSunShine, Commented Apr 3, 2018 at 6:48
The orignal question shows a backslash character '\', the string in the comment shows a forward slash character '/'. Those are two different characters. The backslash character is a special character in the context of a MySQL string literal. To get a literal backslash character in a string literal, we need to two escape the backslash with another backslash character... two backslashes, e.g. SELECT '\\'. — spencer7593
– spencer7593, Commented Apr 3, 2018 at 16:50

spencer7593 · Accepted Answer · 2018-04-03 06:49:24Z

3

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)%'

edited Apr 3, 2018 at 6:49

answered Apr 3, 2018 at 6:47

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tim Biegeleisen Over a year ago

Nice catch...I missed that +1

EternalSunShine Over a year ago

Above SQL not working... I used below SQL as well and its giving 0 records ... select * From software where path like '%C:\\%' ;

EternalSunShine Over a year ago

BTW I am using Maria 5.5.6 and Workbench as client.

spencer7593 Over a year ago

@Shatru: we would expect the SQL to return 0 rows when the table does not contain any rows that satisfy the predicates in the WHERE clause. Again, I recommend testing expressions (including string literals) in a SELECT statement For example, what value does this query return SELECT '%C:\\%' ? Is workbench or something else in the execution path interpreting the string and consuming backslashes? Try SELECT '%C:\\\\%', '%C:\\\\\\\\%' to figure out how many backslashes are needed in the original string in your client in order to get a single backslash character in MySQL/MariaDB.

spencer7593 Over a year ago

@Shatru: In some bash shell scripts, using heredocs and calling mysql command line client, I have to use eight (8) backslash characters to get two backslash characters in SQL text passed to MySQL, so that MySQL sees a single backslash character.

Collectives™ on Stack Overflow

Mysql Windows path update

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related