Correct Syntax for MariaDB Update Statement

Question

I am in the process of updating some old Access/SQL queries to run in a MariaDB environment. The following update statement is causing me problems. I've been working through it, but still haven't resolved it. I did try replacing Nz with COALESCE(). I also made note that StrComp is now STRCMP in MariaDB. It seems that STRCONV doesn't exist in MariaDB.

My question is: what does the following code do, and how can I reproduce this in a MariaDB-friendly way?

UPDATE ft.contacts
SET firstname = StrConv(firstname,3)
WHERE (((firstname)>"") 
AND ((StrComp(UCase(Nz(firstname)),Nz(firstname),0))=0)) 
OR (((firstname)>"") 
AND ((StrComp(LCase(Nz(firstname)),Nz(firstname),0))=0));

As far as I know, MariaDB does not have a "proper case" function, so you cannot directly convert this code. — Gordon Linoff, Oct 15 '19 at 12:55
update your question add a proper data sample and the expected result — ScaisEdge, Oct 15 '19 at 13:03
you could write a function that does the 'proper case' conversion, examples here https://stackoverflow.com/questions/6181937/how-to-do-a-proper-case-formatting-of-a-mysql-column — Benoit F, Oct 15 '19 at 13:03

score 1 · Accepted Answer · answered Oct 15 '19 at 13:04

1

for mimic the Capitalize function you could use

CONCAT(UCASE(LEFT(firstname, 1)), SUBSTRING(firstname, 2));

answered Oct 15 '19 at 13:04

ScaisEdge

131,976
10
91
107

Thanks, this with a WHERE clause to check for `""` works in my case. – Muirik Oct 15 '19 at 13:19

Correct Syntax for MariaDB Update Statement

1 Answers1