Replace the special characters

Question

data = "Qwsdyz_qwrbc_bcD_qwEr"

What I need is:

remove all the _
all characters to be in lower
the starting letter should be caps for all the 4 like this (QwsdyzQwrbcBcdQwer)
whatever changes made in above statement should't change the result like if we changed like Qwsdqwqyz_qwrwqeqwebc_bcqwD_qqwwEr_dadakjas i need the result like QesdqwqyzQwrwqeqwebcBcqwdQqwwerDasakjas

Please help me with MySQL coding.

set @data="Qwsdyz_qwrbc_bcD_qwEr";
select lower(SUBSTRING_INDEX(@data,"_",1)) into @data1;
select ucase(left(SUBSTRING_INDEX(@data,"_",2),1)) into @data2;
select lower(SUBSTRING_INDEX(@data,"_",2)) into @data3;
select substring(reverse(SUBSTRING_INDEX(reverse(@data3),"_",1)),2) into @data4; 
select reverse((lower(SUBSTRING_INDEX(@data,"_",3)))) into @data5;
select (reverse(SUBSTRING_INDEX(@data5,"_",1))) into @data6;
select ucase(left(@data6,1)) into @data7;
select substring(@data6,2) into @data8;
select reverse(@data) into @data9;
select reverse(lower(SUBSTRING_INDEX(@data9,"_",1))) into @data10;
select ucase(left(@data10,1)) into @data11;
select substring(@data10,2) into @data12;
select concat(@data1,@data2,@data4,@data7,@data8,@data11,@data12) data;

Please update the question with the code you have already tried. — Cosmin Staicu, Dec 20 '19 at 10:03
If you are new, you might want to read the "how to post a question" Also i kinda miss what you tried yourself. — Dorvalla, Dec 20 '19 at 10:55

score 0 · Answer 1 · answered Dec 20 '19 at 10:14

You can use split functions replied in this question. Do sub-string and find every splited strings first character with UPPER() function do it upper character and LOWER() function do other characters to lowercase. And finally join with CONCAT() function.

Gordon Linoff · Answer 2 · 2019-12-20T13:38:44.177

0

This is just a messy concat() and substring_index():

select concat(concat(upper(left(lower(substring_index(data, '_', 1)), 1)),
                     lower(substr(lower(substring_index(data, '_', 1)), 2))
                    ),
              concat(upper(left(lower(substring_index(substring_index(data, '_', 2), '_', -1)), 1)),
                     lower(substr(lower(substring_index(substring_index(data, '_', 2), '_', -1)), 2))
                    ),
              concat(upper(left(lower(substring_index(substring_index(data, '_', 3), '_', -1)), 1)),
                     lower(substr(lower(substring_index(substring_index(data, '_', 3), '_', -1)), 2))
                    ),
              concat(upper(left(lower(substring_index(substring_index(data, '_', 4), '_', -1)), 1)),
                     lower(substr(lower(substring_index(substring_index(data, '_', 4), '_', -1)), 2))
                    )
             )
from (select 'Qwsdyz_qwrbc_bcD_qwEr' as data) x

SQL is not optimized for string manipulations. I would advise you to do this in another tool, such as Python, if that is possible.

Here is a db<>fiddle.

edited Dec 20 '19 at 13:38

answered Dec 20 '19 at 11:50

Gordon Linoff

1,242,037
58
646
786

Thank You i will try it out! there is no change happening its show the data as it is. – Manickam Dec 20 '19 at 12:06
@Manickam . . . The code works. I added a db<>fiddle. – Gordon Linoff Dec 20 '19 at 12:59
I need answer like this "qwsdyzQwrbcBcdQwr" – Manickam Dec 20 '19 at 13:33
@Manickam . . . You don't need the `concat_ws()` then. I updated the answer. – Gordon Linoff Dec 20 '19 at 13:39
Thank you ! but if i change any character or add will it affect the query – Manickam Dec 20 '19 at 13:46
@Manickam . . . This will work for any string with three underscores that you apply it to. – Gordon Linoff Dec 20 '19 at 13:54
thank you bro..! can you rewrite the same as i can add or change with no restriction – Manickam Dec 20 '19 at 13:56

Replace the special characters

2 Answers2