Replace letters with asterisks except first and last character using MYSQL

Question

How can I Replace letters with asterisks except first and last character for example: house -> h***e Database

<div id="ld">    
        <?php
    $base=mysqli_connect('localhost','root','','exercise');
    $request="select text from exercise";
       /*
       Here I want to show all words from database but with letters replaced
       with asterisks (expect first and last letter)
      */
    mysqli_close($base)
        ?>
  </div>

Is each `text` column value retrieved from your db a collection of words or just one word? What have you tried? — user1191247, Dec 04 '21 at 20:26
Please add your modified code to you question and say what does or does not work. What errors are you getting? — user1191247, Dec 04 '21 at 20:54
Trouble is, if you have a two letter word, there is would be no asterisks applied. Consider this: https://stackoverflow.com/a/43904214/2943403 — mickmackusa, Dec 15 '21 at 13:18

Garr Godfrey · Accepted Answer · 2021-12-04T21:01:32.417

There is a way to do it in mysql. Other answers may give PHP solution, but in mysql:

SELECT CONCAT(LEFT(text,1), REPEAT('*',LENGTH(text)-2), RIGHT(text,1)) AS text FROM exercise

simply concatenate the first letter, then your asterisks, then last letter.

Adding the PHP code just in case:

// New Connection
$db = new mysqli('localhost','user','pass','database');


$result = $db->query("SELECT CONCAT(LEFT(text,1), REPEAT('*',LENGTH(text)-2), RIGHT(text,1)) AS text FROM exercise");
if($result){
    // Cycle through results
    while ($row = $result->fetch_object()){
        echo $row;
    }
    // Free result set
    $result->close();
}

alternatively, doing the replacement in PHP 7.1 or later

// New Connection
$db = new mysqli('localhost','user','pass','database');


$result = $db->query("SELECT text FROM exercise");
if($result){
    // Cycle through results
    while ($row = $result->fetch_object()){
        if (strlen($row) > 2) {
           echo  $row[0] . str_repeat('*', strlen($row)-2) . $row[-1]
        }
        else {
           echo $row
        }

    }
    // Free result set
    $result->close();
}

What happens if the word is one letter long? What happens if the word is two letters long? — mickmackusa, Dec 15 '21 at 13:20
@mickmackusa in the PHP version, it leaves the original unaltered. In the mysql version, works fine for 2 character string or zero length string. For a 1 character string, it will double it, so 'a' becomes 'aa' — Garr Godfrey, Dec 15 '21 at 19:05

Replace letters with asterisks except first and last character using MYSQL

1 Answers1