SQL Problem

Discuss Programming / Linux questions.

Moderators: scallenger, Sky, TresCom Support Team

Post Reply
User avatar
Nick3069
Albertosaurus
Albertosaurus
Posts: 2361
Joined: Wed May 16, 2007 10:32 pm
Location: Ontario, Canada

SQL Problem

Post 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.
Dragonlord
Compsognathus
Compsognathus
Posts: 1070
Joined: Fri Jan 04, 2008 12:14 am
Location: Switzerland
Contact:

Re: SQL Problem

Post 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.
Image
Leader, Head Programmer: Epsylon | Drag[en]gine ( Wiki )
User avatar
Nick3069
Albertosaurus
Albertosaurus
Posts: 2361
Joined: Wed May 16, 2007 10:32 pm
Location: Ontario, Canada

Re: SQL Problem

Post 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?
Dragonlord
Compsognathus
Compsognathus
Posts: 1070
Joined: Fri Jan 04, 2008 12:14 am
Location: Switzerland
Contact:

Re: SQL Problem

Post 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.
Image
Leader, Head Programmer: Epsylon | Drag[en]gine ( Wiki )
Post Reply