SQLite Forum

Capitalize first letters only
Login

Capitalize first letters only

(1) By anonymous on 2020-08-05 14:43:51 [link] [source]

Is there some function / method to capitalize only the first letter of each word in a text field?

I looked at upper() for a possible optional parameter to do this but no.

I also looked at printf but didn't see a relevant format.

Any other way? Perhaps, a loadable extension someone has written and willing to share?

Example:

my name

should become:

My Name

Thank you.

(2.1) By Gunter Hick (gunter_hick) on 2020-08-05 15:02:27 edited from 2.0 in reply to 1 [link] [source]

That kind of operation is best done at application level. SQL does not lend itself well to string manipulation. The requested operation is a one-liner in Perl for example.

(3) By Ryan Smith (cuz) on 2020-08-05 16:10:43 in reply to 1 [source]

If by some cosmic accident you want this as a once-off DB operation or you happen to use SQLiteSpeed for a DB manager, then there is an SQL function called "TitleCase()" that you can use which will render:

  • TitleCase('JOHNNY') --> 'Johnny'
  • TitleCase('john smith') --> 'John Smith'
  • TitleCase('jOhNnY b. GOODE') --> 'Johnny B. Goode'
  • TitleCase('my name') --> 'My Name'
  • etc.

Failing that, the best option is to add such a UDF to the SQLite library that you are using as explained here.

Note some base peculiarities with the above function (or things to consider if you are making your own) that may or may not be desired:

  • TitleCase('joe McAdam') --> 'Joe Mcadam'
  • TitleCase('Mary-joe smith') --> 'Mary-Joe Smith'
  • TitleCase('JK. Rowling') --> 'Jk. Rowling'

Other than that, as Gunther mentioned, best to do it inside your application code.

Good luck!

(4) By anonymous on 2020-08-06 19:06:20 in reply to 3 [link] [source]

Thanks for the SQLitespeed pointer. Yes, that was a one-off job (for now).

Unfortunately, the app has too many issues to replace my other browser (sqlitebrowser), like throwing errors when there should't be any (even after updating its sqlite3.dll with mine to be one same page), or not updating the view after running an UPDATE SQL.

But, I somehow managed to complete the job eventually. So, thanks.

(5) By anonymous on 2023-09-28 12:37:02 in reply to 3 [link] [source]

select printf("%s%s", substring(name,1, 1), lower(substring(name,2)))
from users;

(6) By Warren Young (wyoung) on 2023-09-28 12:46:22 in reply to 3 [link] [source]

The exceptions are myriad.

Atop that, you have regional rules like in France, where surnames are given in all-caps in mailings.

Good luck writing a function that catches them all and handles them all correctly.