Page 1 of 1

SQL Problem

Posted: Thu Nov 27, 2008 9:16 pm
by Nick3069
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:

Code: Select all

SELECT concat(UPPER(DISTINCT left(name, 1)), LOWER(DISTINCT Right(name, length(name)-1))) FROM saleM
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()

Code: Select all

SELECT DISTINCT left(name, 1) FROM saleM
SELECT DISTINCT Right(name, length(name)-1) FROM saleM
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.

Re: SQL Problem

Posted: Thu Nov 27, 2008 9:44 pm
by Dragonlord
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.

Re: SQL Problem

Posted: Fri Nov 28, 2008 12:15 am
by Nick3069
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.
Dragonlord wrote: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.
Is there no way to do it with a standard SQL command?

Re: SQL Problem

Posted: Fri Nov 28, 2008 12:39 am
by Dragonlord
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.