I have a table with a series of permissions, and I need to change every user's permission to Y in a given position p in the string, is there a SQL command I can use or do I have to write a program/script to do it?
Add a comment
|
2 Answers
You can use a combination of concat and substring in MySQL:
mysql> select concat(substring('12345',1,3),'A',substring('12345',5));
+---------------------------------------------------------+
| concat(substring('12345',1,3),'A',substring('12345',5)) |
+---------------------------------------------------------+
| 123A5 |
+---------------------------------------------------------+
You can replace '12345' with the name of your column.
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
1 Comment
Peter
Suggest to use insert() function. Guido seems to perform a change of one char per one other. So insert() will be right. Using substring() will be right if wanted to change eg 5 char by 10 others so when source-length != dest-length.
Just use the insert() function. You give it the source string, the string you want to insert and the char length. Like:
UPDATE TAB_SAP SET tsap_xgen = INSERT(tsap_xgen, 52, 1, '0') WHERE tsap_unidade = '1392398397' AND SUBSTR(tsap_xgen,52,1) != '0'
In this case, I search a record of a firefighter (Sapador) which belong to a company (unidade) and which as a specific flag in a string (the SUBSTR(tsap_xgen,52,1) != '0' part of the WHERE clause), then I update this string to change the char to '0'. Using insert() you can change more than one char, eg INSERT(field_name, 52, 5, 'Hello')
Notice the name of the function is "strange" as it not "insert" but "erase" the char at the location to put the new chars.
Comments
