MySQL chooses where Uppercase follows in lowercase

Question

I have a table containing many names formatted as:

Max.Example 

I wanted to replace. with a space, but I accidentally replaced it with nothing, so they are all like this: MaxMuster

I cannot restore a backup or rollback. The only way I found is to insert Space all over the world, and Capital Letter after the usual one. But what is the command for?

Answer 1

I found the answer based on the Bruteforce script from Mark Bannister

 UPDATE TABLE SET COLUMN = ltrim( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace(COLUMN ,'zA','z A') ,'zB','z B') ,'zC','z C') ,'zD','z D') ,'zE','z E') ,'zF','z F') ,'zG','z G') ,'zH','z H') ,'zI','z I') ,'zJ','z J') ,'zK','z K') ,'zL','z L') ,'zM','z M') ,'zN','z N') ,'zO','z O') ,'zP','z P') ,'zQ','z Q') ,'zR','z R') ,'zS','z S') ,'zT','z T') ,'zU','z U') ,'zV','z V') ,'zW','z W') ,'zX','z X') ,'zY','z Y') ,'zZ','z Z') ); 

It works for everything.

 MaxExample = Max Example MaxExampleTest = Max Example Test MaxExampleTestTT = Max Example Test TT 

Just repeat this 26 times for all letters.

Answer 2

Try this procedure ...

 create procedure updateName() begin declare cnt, len, val, flag int; declare newName, oldName varchar(30); select count(*) into cnt from tbl; set cnt =cnt-1; while cnt >= 0 do set flag=0; select details into oldName from tbl limit cnt, 1; select length(oldname) into len; while flag=0 and len > 0 do select ascii(substring(oldname, len)) into val; if val < 90 then select concat(substring(oldname, 1, len-1), ' ', substring(oldname,len)) into newname; update tbl set details = newName where details = oldname; set flag=1; end if; set len = len - 1; end while; set cnt = cnt-1; end while; end// 

Fiddle

EDIT

For multiple char caps To solve the problem with multiple char caps

 create procedure updateName() begin declare cnt, len, val, flag int; declare newName, oldName varchar(30); select count(*) into cnt from tbl; set cnt =cnt-1; while cnt >= 0 do set flag=0; select details into oldName from tbl limit cnt, 1; select length(oldname) into len; while len > 1 do select ascii(substring(oldname, len)) into val; if val < 90 then select concat(substring(oldname, 1, len-1), ' ', substring(oldname,len)) into newname; update tbl set details = newName where details = oldname; set oldname=newname; end if; set len = len - 1; end while; set cnt = cnt-1; end while; end// 

Fiddle

Back up the table before starting this process.

Hope this helps ...

Answer 3

Brute force is applied here:

 select ltrim( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace(myColumn ,'A',' A') ,'B',' B') ,'C',' C') ,'D',' D') ,'E',' E') ,'F',' F') ,'G',' G') ,'H',' H') ,'I',' I') ,'J',' J') ,'K',' K') ,'L',' L') ,'M',' M') ,'N',' N') ,'O',' O') ,'P',' P') ,'Q',' Q') ,'R',' R') ,'S',' S') ,'T',' T') ,'U',' U') ,'V',' V') ,'W',' W') ,'X',' X') ,'Y',' Y') ,'Z',' Z') ) from myTable 

Answer 4

Here is a different approach. Fit the UNION query to the maximum column length. He also has the opportunity to improve.

 select details, group_concat(t2.c1) as new_value from ( Select details, case when n = 1 then substr(details,n,1) when ascii(substr(details,n,1)) between ascii ('A') and ascii ('Z') then concat (' ', substr(details,n,1)) else substr(details,n,1) end as c1 ,n FROM tbl, (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 ) as tbl_1 where substr(details,n,1) is not null ) as t2 group by details 

Answer 5

A small start should be to use REGEXP to check for a letter followed by a capital letter (move to WHERE):

 SELECT field REGEXP BINARY '[az]{1}[AZ]{1}' FROM test 

but again, this is not a complete answer, but just an idea.

Answer 6

In this situation, a terribly crude way to do this can help. Use the giant case to find where the first capital letter is:

 select (case when pos is null then field else concat(substr(field, 1, pos - 1), '.', substr(field, pos)) from (select (case when ascii(substr(col, 2, 1)) between ascii('A') and ascii('Z') then 2 when ascii(substr(col, 3, 1)) between ascii('A') and ascii('Z') then 3 when ascii(substr(col, 4, 1)) between ascii('A') and ascii('Z') then 4 when ascii(substr(col, 5, 1)) between ascii('A') and ascii('Z') then 5 when ascii(substr(col, 6, 1)) between ascii('A') and ascii('Z') then 6 when ascii(substr(col, 7, 1)) between ascii('A') and ascii('Z') then 7 when ascii(substr(col, 8, 1)) between ascii('A') and ascii('Z') then 8 when ascii(substr(col, 9, 1)) between ascii('A') and ascii('Z') then 9 when ascii(substr(col, 10, 1)) between ascii('A') and ascii('Z') then 10 end) as pos, field from t ) t 

I'm not sure if you need the appropriate update statement or just logic to search for a position for a period.