check for whitespace fails

Published 7 months ago by arecuk

Hey guys,

I have external database that I connect to from laravel. This external table has a field: rsID varchar40 utf8_general_ci

The right format of rsID is 'rs123'. However, in some case based of user-error when entering new raws the ID contains a space:

' rs123' - the space at the beginning

'rs123 ' - the space at the end

The space is stored in the table and I can see it using phpmyadmin.

Then, in my laravel app I query the table and indeed I get the rsID rendered with a space.

I want to identify those rsIDs that contain space but Im having hard time with it.

when I hardcode the variable all works just fine!
$x = ' xx';
if (strlen($x) != strlen(trim($x)))
 there is space in the string!

but when I get the variable as a result of database query it fails:
foreach ($data as $rsID => $alleles):
if (strlen($rsID ) != strlen(trim($rsID )))
 although rsID contains space the condition is evaluated as false

Does any one has some idea? I've been playing with it for hours but somehow cant figure it out.

Thanks a lot, Tomas

Best Answer (As Selected By arecuk)
  • Try and paste it in there. Maybe it shows you want kind of character it is. It could be anything.
  • I think there is no easy mysql update query to do a preg_replace function. So you could just run a simple select query. Detect the malformed ones and update them with the response of what preg_replace('/[^a-zA-Z0-9]/','', $snp->rs_id) gives you.
  • And to prevent this in the future you should strip all unwanted characters before you save the object: $input = preg_replace('/[^a-zA-Z0-9]/','', $input)

I just opened your sql file and changed the character encoding of the file to something else. For windows-1250 it gives me:  rs1800795o it is definitely not a space.


I think you are using the strlen of the key you should use the value instead


if (strlen($alleles ) != strlen(trim($alleles )))



i don't know why the code is not showing correctly


thank you but I need to check the value of $rsID indeed

check the screenshot here:


Actually i just saw what you did, for example

$var = "a";
strlen($var); // RETURN 1;
$var2 = "a      ";
strlen($var2); // RETURN 6;
strlen(trim($var2)); // RETURN 1;

yes this code works but it does not when I retrieve the variable as a result of database query


Yes, can i see the database query?

7 months ago (78,940 XP)

I don't know if this is helpful for you but if you just want to remove all whitespaces around the character you could use this query if you have the rights to do that:

UPDATE your_table SET rsID = TRIM(rsID)

That will remove all leading and trailing whitespaces from your value. See for more options and details


Dont forget too that you are looping, so the if will return the result of the condition of the last value


@m-rk - well the query returns 0 raws affected, although there are rsID with spaces

@ismailm - good point but I use this code:

if (strlen($rsID) != strlen(trim($rsID))): dd ($rsID); endif;

7 months ago (78,940 XP)

@arecuk Your code looks ok at first sight. Are you 100% sure that the rsID isn't already trimmed somewhere? Can you maybe dump($rsID) of a value that you are 100% sure that must be incorrect?

There is no real need for doing the strlen check btw. You could also just compare the trimmed value with the untrimmed value.

if (trim($rsID) !== $rsID)) {


@arecuk, reply on your query with 0 results... That is odd. Than it looks like the space you see is not a real space but some other fancy utf-8 character maybe?

update 2 you could also try the following. This removes all non alphanumeric values and compares it with the original:

if (preg_replace('/[^a-zA-Z0-9]/','', $rsID) !== $rsID) {

7 months ago (1,043,405 XP)

cant you use like to find each variant?

->where('rsID', 'like', ' %')
7 months ago (796,540 XP)

Are you sure it's actually "spaces" in the db and not  ?


it might not be spaces.. like cronix say.. u should do a var_dump() to see what it actually is... damn he beat me to it..


thank you for all your comments ... I think it could something else than a space, although I can not figure it what else could it be?

in the database there is only space, not   ... var_dump does not help either

here is a screenshot from phpmyadmin:

and here dump of my table with the a few entries with spaces as shown on the picture above:


@m-rk great, that helps somehow!

following code identify the rsID with a space:

if (preg_replace('/[^a-zA-Z0-9]/','', $snp->rs_id) !== $snp->rs_id):
                dd ($snp->rs_id);

now the question is:

  • what is the space actually? it could be some fancy utf-8 character, as mentioned by @m-rk ...
  • how to remove it?

thanks a lot, I feel we are getting there!

Please sign in or create an account to participate in this conversation.