TresCom Forums

I'm trying to change the cases so the first letter is upper case and the rest are lower case, but this is closest I could get:


It gives me an error and I have no idea what the problem is and I've searched around the internet and found nothing. It works until I add a concat() or UPPER()/LOWER()

They both work.

I know this web site is patially made with SQL and there are quite a few programmers here, so I thought maybe I could get help here.

SELECT is a projection operator selecting which fields to display in the output. Concat though produces an intermediate value which is not a table field anymore. To do this you need to alias the result to make a virtual table field hence:
SELECT concat(UPPER(DISTINCT left(name, 1)), LOWER(DISTINCT Right(name, length(name)-1))) AS myNewField FROM saleM

That said it's bad to do higher logic in queries. Better fetch just "SELECT name FROM saleM;" and concat them in your programming language.

_________________

Leader, Head Programmer: Epsylon | Drag[en]gine ( Wiki )

Actually, I want to UPDATE the values, but I want to test the command with SELECT first. I don't want to ruin the database.

No, that's not it. I already tried using "AS", but it didn't work. I think the problem is related to the combination of CONCAT() and DISTINCT.


Is there no way to do it with a standard SQL command?

SQL has not been made for this kind of task. It is primary a query language. Data mangling ( as you do here with concatenation and case fiddling ) is not the business of SQL at all. It is doable but not optimal. Now what goes for the example I didn't see the DISTINCT clause. It's anyways a clause that should not be used on generated values since it's a MySQL-hack and violates the relational principle ( which governs that the result returns is always distinct ). I would not recommend you trying to do this in SQL at all. Better do a SELECT first obtaining the values into an array. Then do the fudging of the values and then UPDATE it back. Another note is that UPDATE replaces ALL matching records with the provided value. You can not write three different values to three different records in one UPDATE. You have to use one UPDATE query for each record you want to update hence you are better off with the programming solution.

_________________

Leader, Head Programmer: Epsylon | Drag[en]gine ( Wiki )