TresCom Forums

TresCom : The Trespasser Fan Gateway...
It is currently Sun Oct 21, 2018 5:27 pm

All times are UTC




Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: SQL Problem
PostPosted: Thu Nov 27, 2008 9:16 pm 
Offline
Albertosaurus
Albertosaurus
User avatar

Joined: Wed May 16, 2007 10:32 pm
Posts: 2059
Location: Ontario, Canada
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 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 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.


Top
 Profile  
 
 Post subject: Re: SQL Problem
PostPosted: Thu Nov 27, 2008 9:44 pm 
Offline
Compsognathus
Compsognathus
User avatar

Joined: Fri Jan 04, 2008 12:14 am
Posts: 1066
Location: Switzerland
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 )


Top
 Profile  
 
 Post subject: Re: SQL Problem
PostPosted: Fri Nov 28, 2008 12:15 am 
Offline
Albertosaurus
Albertosaurus
User avatar

Joined: Wed May 16, 2007 10:32 pm
Posts: 2059
Location: Ontario, Canada
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?


Top
 Profile  
 
 Post subject: Re: SQL Problem
PostPosted: Fri Nov 28, 2008 12:39 am 
Offline
Compsognathus
Compsognathus
User avatar

Joined: Fri Jan 04, 2008 12:14 am
Posts: 1066
Location: Switzerland
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 )


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  

Powered by phpBB® Forum Software © phpBB Group