CODEWITHSUNDEEP
mysqlsqldatabase
Ron
Ron

Reputation: 55

Trying to find all field starting with space

My DB is acting a little weird and I figured out that coin_id of some products has a "space" as their first letter. So instead of "123456" it is " 123456". I am trying to find all the coin_id that start with a "space" and then remove the spaces.

Upvotes: 1

Views: 932

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

First, why is an id that consists of digits stored as a string? Perhaps the "12345" is not really a representative value.

I would suggest that you use like:

update t
    set coin_id = trim(coin_id)
    where coin_id like ' %';

Notes:

  • like ' %' allows the optimizer to use an index on t(coin_id).
  • The query uses trim(). After spaces, at the end could also be a problem.
  • If the value is digits, then it should be stored as a number of some sort.

Upvotes: 0

AbraCadaver
AbraCadaver

Reputation: 78994

No need to find them yourself, just trim them in an UPDATE query. Use LTRIM or just TRIM if there shouldn't be leading or trailing spaces:

UPDATE table_name SET coin_id = LTRIM(coin_id)

As Dudu Markovitz points out, especially if there are few to update of many, you can find them and update them for possibly better performance:

UPDATE table_name SET coin_id = LTRIM(coin_id) WHERE coin_id LIKE ' %'

Or for TRIM:

UPDATE table_name SET coin_id = TRIM(coin_id)
       WHERE coin_id LIKE ' %' OR coin_id LIKE '% '

Upvotes: 3

Antony
Antony

Reputation: 1273

How about this? Removing one character from the columns starting with a space

UPDATE table
    SET coin_id = RIGHT(coin_id,LENGTH(coin_id)-1)
    WHERE LEFT(coin_id,1) = ' ';

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

Update has performance Implications.
Update only what you need to update.

update t
set    coin_id = ltrim(coin_id)
where  coin_id like ' %'

Upvotes: 1

Related Questions