CODEWITHSUNDEEP
mysql
Guido
Guido

Reputation: 2634

Changing a specific char in a MySQL string

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?

Upvotes: 5

Views: 3484

Answers (2)

Peter
Peter

Reputation: 1348

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.

Upvotes: 1

GoldenNewby
GoldenNewby

Reputation: 4452

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

Upvotes: 7

Related Questions