CODEWITHSUNDEEP
mysqlsqltruncate
tylwright
tylwright

Reputation: 33

Truncating a SQL char value

I hope that someone can tell me what I am doing wrong.

I would like to query my SQL table and receive back a ClassID and a ClassName.

The ClassID is a char(8) and holds values such as 123.45L1 or 350.12.

I am attempting to shorten that value so that I receive back 123 or 350 only.

Here is my code:

    $classSelect = "SELECT LEFT(ClassID , 3), ClassName FROM Class GROUP BY ClassID";   
    $result = mysql_query($classSelect);

    echo "<td><select multiple size='10' name='Class'>";

    while ($row = mysql_fetch_array($result)) { 
        echo "<option value='Choice'" . $row['ClassID'] . " - " . $row['ClassName'] . "'>" . $row['ClassID'] . " - " . $row['ClassName'] . "</option>";
    }
    echo "</select></td>";

Upvotes: 1

Views: 414

Answers (2)

eggyal
eggyal

Reputation: 125835

If you are seeking to extract the string up to the decimal point, and that might not always be in the same place, you can use MySQL's SUBSTRING_INDEX() function:

SELECT SUBSTRING_INDEX(ClassID, '.', 1) FROM Class

If you are seeking to extract the integer up to the first non-decimal character, you can simply CAST() the string and MySQL will do the rest:

SELECT CAST(ClassID AS UNSIGNED) FROM Class

The benefit of this latter approach is that the resulting column will be of the correct datatype.

Upvotes: 1

Nedret Recep
Nedret Recep

Reputation: 728

SELECT LEFT(ClassID , 3) AS somename, ClassName

and then refer it from PHP as $row['somename']

Upvotes: 1

Related Questions