SQL-Search for words that are all Uppercase and change them to first letter Capital

Question

I have sentences in one a Column of the mySQl, I need to search fro the words that are all uppercase (for example "My WORD") and change them to first letter Capital (for example "My Word")

I have tried the following from other questions in Stackoverflow, but non of them works When I use

UPDATE [yourtable]
SET word=UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word)))

I get

"Databasename.LEN does not exist"

and when I use

UPDATE [yourtable]
    SET word=UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2));

OR

UPDATE [yourtable]
    SET word=UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LENGTH(word)))

It changes my column value to all "0"s and "1"s.

I also tried Concat UPDATE [yourtable] SET word =Concat(UPPER(LEFT(word,1)), LOWER(SUBSTRING(word,2))); What it does is it only capitalizes the first word of the whole sentence , so now I am getting "My word"

this is tough if they are actual sentences. You will do better with php. — Vamsi Prabhala, May 04 '17 at 01:42
@vkp, this needs to be done in SQL directly in database, it will be used to get a csv file out of the table — Schwann, May 04 '17 at 01:43
"None of them work" - well, give us an example input and what the code outputs. e.g. `DECLARE @word nvarchar(100) = 'test STRING'; SELECT UPPER(LEFT(@word,1)) + LOWER(SUBSTRING(@word,2,LEN(@word)))` gives `Test string` as expected in Sql Server — Zac Faragher, May 04 '17 at 01:44
It _does_ work in SQL, it may not work in your _flavour_ of SQL. I'm not saying it would suit your needs/solve your problem, it's purely for test purposes. You still haven't given any samples of your inputs/outputs, so it's very hard to debug what's going on. — Zac Faragher, May 04 '17 at 01:51
@ZacFaragher, I did give sample in the question, I have sentences like "My WORD" in a Column and I need to change them to "My Word" technically all the letter that are all uppercase needs to change to first letter uppercase only. — Schwann, May 04 '17 at 01:53
Yes, and what is the result when you use the code you provided? Is it "My WORD", "my WORD", "My WoRd" ...? Is there a Parsing error? You've given us no example of in what way it doesn't work, while I have given you an example to show that in fact, it does. — Zac Faragher, May 04 '17 at 01:56
Have you seen http://stackoverflow.com/questions/1191605/is-there-a-simple-way-to-convert-mysql-data-into-title-case? — chris85, May 04 '17 at 02:15
@Schwann `LEN` doesn't exist in mysql, have you tried `LENGTH` instead? — Zac Faragher, May 04 '17 at 02:33
@chris85 I'm not sure about `MySQL` but when using user-defined functions in `SQL Server`, it can force queries to go serial instead of parallel, severely hurting query performance. I'd try and steer clear of that unless absolutely necessary. — Zac Faragher, May 04 '17 at 02:41
@ZacFaragher, when I use UPDATE [yourtable] SET word=UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LENGTH(word))) , It does the same thing as the second query and change all the values to "0"s and "1"s. — Schwann, May 04 '17 at 03:01
@chris85 I did try UPDATE [yourtable] SET word =Concat(UPPER(LEFT(word,1)), LOWER(SUBSTRING(word,2)));` What it does is it only capitalizes the first word of the whole sentence , so now I am getting "My word" — Schwann, May 04 '17 at 07:07
That was the reason why you were getting 1s and 0s, not an answer. I'd use the linked thread. You need to iterate over the `word` to split it on every space. You current query only takes the first character, capitalizes it, then lowercase all other characters. If you have a space or number as the first character I think this also would fail you. Did you try the user function? — chris85, May 04 '17 at 12:53
@chris85, I do not understand the link thread and also I do not know how to use user function, thank you in advance for explaining it. — Schwann, May 04 '17 at 16:13

SQL-Search for words that are all Uppercase and change them to first letter Capital

0 Answers0