How to I replace the first character of a string only if it's a space in Mariadb (MySQL)-CodePudding

So I have a bunch of contacts in a database whose first names begin with a space. I want to replace the first character of all contacts if it's a space with nothing (I mean '').

For now I have:

UPDATE contact
SET SUBSTRING(contact.FirstName, 1, 1) = ''
WHERE contact.FirstName LIKE ' %'

It says that I have a syntax error and I'm not sure why. Unfortunately I'm not exactly sure how to ask this question so I find nothing online.

What should I do instead? Thanks in advance.

CodePudding user response：

You want LTRIM:

UPDATE contact
SET FirstName = LTRIM(FirstName)
WHERE FirstName LIKE ' %'

CodePudding user response：

SUBSTRING() is a function, the result of a function is immutable, you cannot assign values to a function, but you can do:

SET Contact.FirstName = LTRIM(Contact.FirstName)