CODEWITHSUNDEEP
mysql
Johan Fourie
Johan Fourie

Reputation: 179

MySQL trim() not removing space inside a string

Given the value of name column: AL TAIRAWI TRADING ESTABLISHMENT

Update ae_companies set uniqueidentifier = CONCAT(trim(LEFT(name, 5)), '_', id, '_ae')

Above query produces this:

AL TA_6_ae

What I am looking for is this:

ALTA_6_ae

I have no idea why the trim() isn't working here.

Upvotes: 1

Views: 394

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Use replace() instead of trim().

Update ae_companies 
set uniqueidentifier = CONCAT(replace(LEFT(name, 5), ' ', ''), '_', id, '_ae')

TRIM() isn't working (doing the job you thought it does) in your case because its description is:

Remove leading and trailing spaces

When you do LEFT(name, 5) which produces AL TA all leading and trailing spaces are removed when you wrap this around TRIM() the way you did. In your case space character is neither at the last nor at the first position, so this is why your result doesn't change when trimmed.

What you're looking for is a REPLACE($INPUT_STRING, ' ', '') to truncate all occurences of space character within the input string.

Upvotes: 3

Related Questions