Query Eliminate White Spaces

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have a table with a column named 'vendname'.  This column isn't
completely populated so as I query the column within the table to
populate a pull down on a query page, there are many white spaces.  My
intent is to eventually have all of those empty table cells populated
but until then, would really like to eliminate the white spaces and
hoped I might get some ideas here.  As it currently stands, I have
approximately 50 rows with 15 'vendname' rows populated and a whole
bunch of white space that shows up on the pull down query.

My code is:

$hostName = "localhost";
$userName = "####";
$password = "########";
$dbName = "approvals";
$table = "approvals_tbl";

mysql_connect($hostName, $userName, $password) or die("Unable to
connect to host $hostName");

mysql_select_db($dbName) or die("Unable to select database $dbName");

$query = "SELECT DISTINCT vendname
          FROM $table";
$result = mysql_query($query);

$number = mysql_numrows($result);

   for ($i=0; $i<$number; $i++) {
   $vendname = mysql_result($result,$i,"vendname");
print "<option value=\"$vendname\">$vendname</option>";


any help is greatly appreciated.


Re: Query Eliminate White Spaces

You can add to your query:

    where trim(vendname) <> ''

On Mar 26, 10:08 pm, jc...@lycos.com wrote:
Quoted text here. Click to load it

Re: Query Eliminate White Spaces

Quoted text here. Click to load it

try you query as SELECT DISTINCT vendname FROM $table where
length(vendname) > 0


Re: Query Eliminate White Spaces

Quoted text here. Click to load it

Forget it, I misread your post....will not work unless you trim the field

my mistake


Re: Query Eliminate White Spaces

jcage@lycos.com wrote:
Quoted text here. Click to load it

Try a MySQL newsgroup, such as comp.databases.mysql.

trim() won't work becuase this is a PHP function, and therefore is
processed BEFORE the MySQL call.  You need a SQL function.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Re: Query Eliminate White Spaces

trim in mysql
is as in, php dql VB Java Python lisp Ruby a 'common string manipulation
http://en.wikipedia.org/wiki/Trim_ (programming) and is coded -about the same

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM]

Returns the string str with all remstr prefixes or suffixes removed. If none
of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed.
remstr is optional and, if not specified, spaces are removed.

mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'
This function is multi-byte safe.

see also ltrim and rtrim
If at first you dont succeed
try try try again
If at first you do succeed
 try not to look surprised

Quoted text here. Click to load it

Site Timeline