normalize text in a field

Question

I have a field with value like this 'DOOR-LEFT' and I want to change this to 'Door-LEFT'.

I came across this query on this site:

UPDATE tbl
SET field1 = CONCAT(UCASE(LEFT(field1, 1)), 
                             LCASE(SUBSTRING(field1, 2)));

The above query changes 'DOOR-LEFT' to 'Door-left'. I do not want anything after the - to be updated. So it should be 'Door-LEFT'.

How can I do this?

Kindly use attribution when using something from this site (or any other site). See [How to reference material written by others](https://stackoverflow.com/help/referencing) — Scratte, Jun 06 '20 at 00:29
I was under the impression that your "You got it. Thanks" meant that you would edit the link and attribution into your Question. Kindly, do not delay this. — Scratte, Jun 08 '20 at 20:29
Would [this answer](https://stackoverflow.com/a/14251430/12695027) by [John Woo](https://stackoverflow.com/users/491243) to "How to lowercase the whole string keeping the first in uppercase in MYSQL" by any change be the source? — Scratte, Jun 09 '20 at 21:04
I came across so many posts on Stack Overflow and few other sites. So I dont remember. When I said, "you got it" I meant moving forward I will. And if you want me to guess, then sure John Woo solution is similar to the other posts I came across. — ccs_2000, Jun 11 '20 at 01:45

score 0 · Accepted Answer · answered Jun 06 '20 at 00:28

You can use sustring_index() to split the string on '-', and then use the logic you already have at hand:

update tbl
set field = concat(
    upper(left(substring_index(field1, '-', 1), 1)),
    lower(substr(substring_index(field1, '-', 1), 2)),
    '-',
    upper(substring_index(field1, '-', -1))
)

The surrounding upper() in the last part of the string is not strictly needed for your sample string, which is all upper case to start with. I left it on purpose in case it might be useful for other cases (and it doesn't hurt anyway).

normalize text in a field

1 Answers1