MySQL select where Uppercase follows Lowercase

Question

I have a table witch contains many Names formatted like:

Max.Example

I wanted to replace the . with a Space but I accidently replaced it with nothing so they are all like: MaxMuster

I cant restore a backup or roll back. The only way thad I found would be to insert a Space everywhere a Capital Letter is after a normal one. But what is the command for that?

@yvytty there is no difference. Doesn't matter. There is only one column. — BlitZ
– BlitZ, Commented May 30, 2013 at 10:25
The fields are normal Text fields and there are maybe other dots but it would not be a problem if the get replaced with spaces. The Table has also other columns but i just have a problem with the names. — user2436057
– user2436057, Commented May 30, 2013 at 10:28
Is it an option to install the regular expression module? launchpad.net/mysql-udf-regexp In that case it's fairly trivial to fix, otherwise not impossible but a bit of a pain. — Wolph
– Wolph, Commented May 30, 2013 at 10:31
@WoLpH I would like it with just with normal SQL. But if there is no other way. — user2436057
– user2436057, Commented May 30, 2013 at 10:42

user359040 · Accepted Answer · 2013-05-30 11:01:31Z

Here's a brute force approach:

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

This is good, i Like it. I now just need to fix it so it doesn't make a Space at the Front and somhow thad ist doesent put a Space when the name is 'MaxExampleMISTER' or so.
@user2436057: The LTRIM at the start should take care of the Leading space, but handling multiple sequential capital letters might be more problematic - I suggest using regexp to identify them, and to update them separately.

Meherzad · Accepted Answer · 2013-05-30 11:59:21Z

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 caps char To solve the prob of multiple caps char

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

Take a back up of your table before running this proc..

Hope this helps....

Like your Way but it breaks when the name has Multible Capital Letters for example: AsminFiddleTest is then AsminFiddle Test I think i need to use Mark Bannister Brute Force Method.
@user2436057 Try the edited one it works for any number of caps char. I have just removed the flag to stop at 1 char.. check the edited answer..
Thanks but IT Still Has a Problem with double Capitals I think BruteForce is the best Way
'AsminFiddleTestTT' gives 'Asmin Fiddle Test T T' not 'Asmin Fiddle Test TT'
This proc will split whenever it finds a caps char and this behavior will be even in the brute force approach...

Stoleg · Accepted Answer · 2013-05-30 12:53:25Z

Here is a different approach. Beef up UNION query upto the max length of you column. It has room for improvement too.

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

marc_s · Accepted Answer · 2016-02-13 14:11:48Z

I found an 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')
            );

This works for everything.

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

Just repeat this 26 times for all letters.

This is both really fast and works flawlessly. Not the neatest, but does the job with quality and speed.

Andrius Naruševičius · Accepted Answer · 2013-05-30 10:35:33Z

0

A minor start would be to use REGEXP to check for a letter followed by capital letter (moving that to WHERE):

SELECT field REGEXP BINARY '[a-z]{1}[A-Z]{1}' FROM test

but again, it is not a full answer, more just an idea.

answered May 30, 2013 at 10:35

Andrius Naruševičius

8,6289 gold badges53 silver badges83 bronze badges

1 Comment

user2436057 Over a year ago

OK it shows me if there is one or not but it doesen't dedect if there are multible cases witch happen in my Table.

Gordon Linoff · Accepted Answer · 2013-05-30 10:47:25Z

A horribly brute force way to do this might help in this situation. Use a 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 corresponding update statement, or just the logic for finding the position for the period.

Collectives™ on Stack Overflow

MySQL select where Uppercase follows Lowercase

6 Answers 6

2 Comments

FIDDLE

EDIT

FIDDLE

5 Comments

Comments

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

EDIT

5 Comments

Comments

1 Comment

1 Comment

Comments

Related