CODEWITHSUNDEEP
mysqlsql
Arun
Arun

Reputation: 1220

SQL : Multiple String Replace (Add a space in front of capital letter(s) starting with 2nd capital letter)

I've updated the Name_Table.column_2 data with wrong strings. Instead of 'John Smith',I've updated with 'JohnSmith'.

Now i would like to replace multiple strings,For Example : 'JohnSmith' as 'John Smith' , 'JohnDoe' as 'John Doe' etc.

I'm not familiar with SQL, can some one help me on how to replace multiple strings all at once.

#Name_Table

Column_1      Column_2
1             JohnSmith
2             JohnSmith
3             JohnDoe
4             JohnSmith
5             WayneRooney
6             JohnDoe
7             WayneRooney
8             JohnSmith
9             WayneRooney
10            JohnDoe

Upvotes: 1

Views: 447

Answers (2)

Tin Tran
Tin Tran

Reputation: 6202

if your names are always gonna be in the format of FirstnameLastname you can do a custom function like this

CREATE FUNCTION breakup_name (fullname varchar(50))
RETURNS VARCHAR(50)
BEGIN
  SET @fullname = fullname,@newname='',@letter='',@capcount=0,@space='';
  WHILE LENGTH(@fullname)>0 DO
    SET @letter = LEFT(@fullname,1);
    SET @space = '';
    IF @letter RLIKE BINARY '[A-Z]' THEN
      SET @capcount = @capcount+1;
      IF @capcount >= 2 THEN SET @space = ' '; END IF;
    END IF;
    SET @newname = CONCAT(@newname,@space,@letter);
    SET @fullname = RIGHT(@fullname,LENGTH(@fullname)-1);
  END WHILE;
  RETURN @newname;
END/

then use an UPDATE like this.

UPDATE table1 SET column_2 = breakup_name(column_2);

sqlfiddle

P.S. In the sqlfiddle I used / as delimiter, you'll have to change that according to your delimiter.

The above function will add a space in front of a capital letters (starting with the 2nd capital letter), so for example if you had TommyJaeSmith, it'll return Tommy Jae Smith.

The logic of this function is very simple, it loops through and looks at each letter. If the letter is a capital letter it increments a capcount, if the capcount is greater than or equal to 2 (meaning 2nd capital letter or after) it adds a space... to be concatenated in front of the letter.

Upvotes: 1

Amit-Inex Patel
Amit-Inex Patel

Reputation: 489

I dont know single query for this situation, but try below method to solve your problem. I'm sure it works fine for you.

$sel = mysql_query('SELECT * FROM Name_Table;');
while($row = mysqli_fetch_array($sel)){
    $string = $row['Column_2'];
    $count = strlen($string);
    $strings = array();
    $i = 0;
    $ii = 0;

    while($i < $count)
    {
        $char = $string{$i};
        if(ereg("[A-Z]", $char, $val)){
            $ii++;
            $s = '';
            $s .= $char;
        } else {
            $s .= $char;
        }
        $strings[$ii] = $s;
        $i++;
    }
    $name_with_space = implode(' ',$strings);
    mysql_query('UPDATE Name_Table SET Column_2="'.$name_with_space.'" WHERE Column_1='.$row['Column_1']);
}

Upvotes: 2

Related Questions