REPLACE a given occurance of a string in a column

Question

I have a column like this:

vars
-----------
|A|B|A|D|AB|B...

I want to replace one var (e.g: the one in 3rd position, which happens to be A) but keep any other A's. The position is determined by a variable. So I should get:

vars
-----------
|A|B|D|AB|B...

I know I should use REPLACE and possibly SUBSTRING_INDEX, but I can't figure out how. Here's what I've tried: Let's say :pos=3;

vars = REPLACE(vars, (SUBSTRING_INDEX(SUBSTRING_INDEX(vars, '|', :pos), '|', -1)), '')

But this actually replaces all other characters and keeps the one I need replaced.

UPDATE: Mind that I don't know what var I want to replace, only its position.

UPDATE 2:

vars = Concat(substring(vars ,1,:pos-1), substring(vars,:pos+2))

Do you want to replace only 'A' specifically, or you want to replace all duplicate characters? — Sisir Ranjan, Apr 03 '17 at 07:04
Your data model is broken. Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutly yes!** — Zohar Peled, Apr 03 '17 at 07:10
Sisir Ranjan, just that one occurrence of A which is in the set position (in the variable $pos for example). — user1542894, Apr 03 '17 at 07:17
@user1542894 look at my ans. replace string with your column name — krishn Patel, Apr 03 '17 at 07:18
Zohar Peled, yes I see. But I've already implemented this model in this situation. I will know in the future. — user1542894, Apr 03 '17 at 07:37

krishn Patel · Answer 1 · 2017-04-03T08:02:05.453

1

if you know position number 6 is your position variable

SELECT  concat(SUBSTRING('|A|B|A|D|G|B',1,6-2),SUBSTRING('|A|B|A|D|G|B',6+1) )

or if you know string at second occurrence

    SELECT REPLACE( '|A|B|A|D|G|B', concat( SUBSTRING_INDEX( '|A|B|A|D|G|B', 
'|A', 2 ) , "|A" ) , SUBSTRING_INDEX( '|A|B|A|D|G|B', '|A', 2 ) )

edited Apr 03 '17 at 08:02

answered Apr 03 '17 at 07:17

krishn Patel

2,579
1
19
30

I don't know the actual var, just its position. – user1542894 Apr 03 '17 at 07:54
what is the position ? – krishn Patel Apr 03 '17 at 07:55
It's a variable. – user1542894 Apr 03 '17 at 07:56
Thank you for your help, both you and Nishanth Matha helped and I would accept both answers if I could, but his came a minute earlier. – user1542894 Apr 03 '17 at 12:15
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/139763/discussion-between-user1542894-and-knowledge). – user1542894 Apr 03 '17 at 12:18
Yours but he edited his first. But seems that I still can't make it work the way I want to using `vars = CONACT(SUBSTRING(vars ,1,:pos-1), SUBSTRING(vars,LOCATE('|','vars', :pos+1)))`, so the question remains open. – user1542894 Apr 03 '17 at 12:39
I've updated the question: "the vars are not always |A and |B etc., but also |AB, |AC... So the position shouldn't always move by one character, but until the next |" – user1542894 Apr 03 '17 at 12:56
add some example so i can understand – krishn Patel Apr 03 '17 at 13:05

Nishanth Matha · Answer 2 · 2017-04-04T00:36:03.327

As @Zohar said, I agree your model is broken. Yet, if you want to achieve, here's an example with locate, substring, concat:

First locate the index of first |A the second |A will always be after it.

locate('|A','|A|B|A|D|G|B', locate('|A','|A|B|A|D|G|B')+1)

-- here +1 denotes after first index

Second find the sub-string after the second occurrence of |A

substring('|A|B|A|D|G|B',locate('|A','|A|B|A|D|G|B', locate('|A','|A|B|A|D|G|B')+1)+2)

--here +2 denotes the length of your string with is |A

Third find the sub-string before the second occurrence of |A

substring('|A|B|A|D|G|B',locate('|A','|A|B|A|D|G|B', 1,locate('|A','|A|B|A|D|G|B')+1)-1)

-- here -1 denotes any thing from the beginning to the charecter before |A

Fourth, concat the string after and before the second occurrence of |A

Concat(
  substring('|A|B|A|D|G|B',locate('|A','|A|B|A|D|G|B',1,locate('|A','|A|B|A|D|G|B')+1)-1),
  substring('|A|B|A|D|G|B',locate('|A','|A|B|A|D|G|B', locate('|A','|A|B|A|D|G|B')+1)+2)
)

EDIT 1: Like in your update if you ready know the position you can simply do:

vars = Concat(substring(vars ,1,:pos-1), substring(vars,:pos+2))

EDIT 2:

With your comment if you need to replace till next | you can do use both location and substring something like:

vars = CONCAT(SUBSTRING(vars ,1,:pos-1), SUBSTRING(vars,LOCATE('|',vars, :pos+1)))

DEMO

`vars = Concat(substring(vars ,1,:pos-1), substring(vars,:pos+2))` This works but I forgot to mention another thing: The vars are not always `|A` and `|B` etc., but also `|AB`, `|AC`... So the pos shouldn't always move by one character, but untill the next `|`. — user1542894, Apr 03 '17 at 09:49
@user1542894 there was a typo in the ans . Try the edit it should work! — Nishanth Matha, Apr 04 '17 at 00:27

user1542894 · Answer 3 · 2017-04-03T14:00:23.543

0

I managed to get the result I wanted by using php instead. I already had $vars exploded into an array in the code and then just used unset($vars[$pos]) to remove the var at the set position.

edited Apr 03 '17 at 14:00

answered Apr 03 '17 at 13:12

user1542894

95
1
10

REPLACE a given occurance of a string in a column

3 Answers3