Home / Questions / 193883
Accepted
Miguel
Asked: 2020-09-05 02:41:53 +0800 CST 2020-09-05 02:41:53 +0800 CST

Show first word of a field in SQL

  • 772

I have the need to get the first word of a data from the table. I have searched for information but I only see to get the characters you want.

<?php
    $usuario = $_SESSION['username'];
    $pass = $_SESSION['password'];

    $results = $mysqli->query("SELECT Nombre, SUBSTRING(Nombre, 1,20) AS palabra, 
                              IdUsuario, Username, Password 
                              FROM Usuarios 
                              WHERE Username='$usuario' AND Password ='$pass'");
    mysqli_set_charset("utf8");
    if($res = $results->fetch_array()) {}

    ?>

For example, in column Nombre, the record contains Nombre Apellido 2Apellido. I need to extract the Nombre.

php

2 Answers

  1. Best Answer

    What you need is to make use of SUBSTRING_INDEX(cadena, delimitador, numero)to find in the string the first part up to the first white space:

    <?php
/* Evitamos inyección SQL */
$usuario = $mysqli->real_escape_string($_SESSION['username']);
$pass = $mysqli->real_escape_string($_SESSION['password']);
/* Cambiamos a UTF-8 *ANTES* de hacer consultas SQL */
$mysqli->set_charset("utf8");

/* Realizamos la consulta SQL */
$results = $mysqli->query("
  SELECT
    Nombre,
    SUBSTRING_INDEX(Nombre, ' ', 1) palabra,
    IdUsuario,
    Username,
    Password
  FROM Usuarios 
  WHERE Username='$usuario' AND Password ='$pass'
");
if($res = $results->fetch_array()) {
    /* Aquí se supone que haces lo necesario con $res['palabra'] */
}
?>

    What the function does is split the string into chunks given by the delimiter and returns the numerofirst chunks. If numeroit's negative, then it returns the numerolatest. In the example I use 1so that it returns only the first part (whatever is before the first delimiter or white space).

    You can see an example online here .

    PS: I have to warn you that the code suffers from serious security problems associated with SQL injection that must be solved with prepared queries or using mysqli::real_escape_string().

    • 1

  2. using an explode() of " " you can get the first word. Cheers!

    <?php
$usuario = $_SESSION['username'];
$pass = $_SESSION['password'];

$results = $mysqli->query("SELECT Nombre, SUBSTRING(Nombre, 1,20) AS palabra, 
                          IdUsuario, Username, Password 
                          FROM Usuarios 
                          WHERE Username='$usuario' AND Password ='$pass'");
mysqli_set_charset("utf8");

while($row = $result->fetch_assoc()) {
    echo explode(" ",$row['Nombre'])[0];
}

?>
    • 0