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

Question

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.

score 1 · Accepted Answer · answered Oct 20 '21 at 14:29

1

You want LTRIM:

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

answered Oct 20 '21 at 14:29

Carlos

1,638
5
21
39

Ok that worked. Thanl you! – Oct 20 '21 at 14:32
If that worked, mark it as answer, please (https://stackoverflow.com/help/someone-answers) – Carlos Oct 20 '21 at 14:34
There's a delay before I can do that, but yes I will do it. – Oct 20 '21 at 14:38

score 0 · Answer 2 · answered Oct 20 '21 at 14:29

0

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)

answered Oct 20 '21 at 14:29

Yván Ecarri

1,661
18
39

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

2 Answers2