Update string based on number text in Single column

Example I have this varchar 'XXXOXOXXOX' and 'XOXOXOXXXX'. I need update the 5th 'X' text to 'O' from total 10 text in single string column. So the result after update will be 'XXXOOOXXOX' and 'XOXOOOXXXX'. Please help.

Tag your question with the database you are using.
– Gordon Linoff
2 days ago

2 Answers
2

Here is what I would do in MySQL:

UPDATE yourTable
SET col = CONCAT(LEFT(col, 4), 'O', SUBSTR(col, 6))
WHERE SUBSTR(col, 5, 1) = 'X';


A more generic ANSI SQL answer would be this:

UPDATE yourTable
SET col = LEFT(col, 4) || 'O' || SUBSTR(col, 6))
WHERE col LIKE '____X%';


In SQL Server, you can do:

UPDATE yourTable
SET col = STUFF(col, 5, 1, '0')
WHERE col = '____X%';


In MySQL,

UPDATE yourTable
SET col = INSERT(col, 5, 1, '0')
WHERE col = '____X%';


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.