How can I change the case of my SQL result?

I have a query that gets data to which fields in a GridView are bound.  The data in the database is in all caps.  I would like to change it all to lower case or -- even better -- proper case or title case.  Can I do this in the query or in the BoundField with something like DataFormatString?  The data are not dates, it's just plain ol' text.  I've only ever used DataFormatString for currency and dates.

My query is below if that is of any assistance.

Ideas?  Thanks!

Jeremy
string strTestSQL = "SELECT [id], [mmap], [mlnam], [mfnam], [madd1] + ', ' + [madd2] + ', ' +[mzip5] as mailingAddress, [macre#], [mhse#] + ' ' + [mstrt] +' ' + [msttyp] as propertyAddress, [mdbook], [mdpage], [rec#alph], [minno#] FROM [tblCamraGIS] WHERE [REC#ALPH] LIKE '%" + txtRECALPH.Text + "%'";

Open in new window

mrcoulsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pssandhuCommented:
You cans use the LOWER() function:
Eg, Select LOWER(FieldName) From Table
P.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScorelliCommented:
Try lower().

There doesn't seem to be a proper() (although I believe Excel offers that).  You could try this (I found it online by doing a quick google for "sql proper case function".  I make no warranties for its effectiveness.

CREATE FUNCTION dbo.pCase
(
    @strIn VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
    IF @strIn IS NULL
        RETURN NULL
 
    DECLARE
        @strOut VARCHAR(255),
        @i INT,
        @Up BIT,
        @c VARCHAR(2)
 
    SELECT
        @strOut = '',
        @i = 0,  
        @Up = 1
 
    WHILE @i <= DATALENGTH(@strIn)
    BEGIN
        SET @c = SUBSTRING(@strIn,@i,1)
        IF @c IN (' ','-','''')
        BEGIN
            SET @strOut = @strOut + @c
            SET @Up = 1
        END
        ELSE
        BEGIN
            IF @up = 1
                SET @c = UPPER(@c)
            ELSE
                SET @c = LOWER(@c)
 
            SET @strOut = @strOut + @c
            SET @Up = 0
        END
        SET @i = @i + 1
    END
    RETURN @strOut
END
GO
 
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would suggest to use (sql) functions like this implementation:
http://stackoverflow.com/questions/230138/sql-server-make-all-upper-case-to-proper-case-title-case
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

pssandhuCommented:
Also, to do Propercase you can do something like this:

Select UPPER(Left([mlnam],1))+LOWER(RIGHT([mlnam],LEN([mlnam])-1)) From yourtable
This extracts the first alphabet, changes it into an upper case and rest of the values into lower case.
P.
0
shru_0409Commented:
CREATE FUNCTION InitCap (
 @string varchar(255)
)  
RETURNS varchar(255) AS
BEGIN
 RETURN upper(left(@string, 1)) + right(@string, len(@string) - 1)
END
0
ITGuy64Commented:
I use the following in an Access Database SQL query:
 Left(UCase(Q.lastname),10) AS Expr_LastName,

  This takes the first 10 characters of the last name and puts it all in uppercase.

  Hope this helps.
0
mrcoulsonAuthor Commented:
Okay, I will try these this afternoon.  I have a staff meeting in a few minutes to which I dare not be late (although I would certainly rather be here at my desk coding and listening to music).

Thanks!

Jeremy
0
pssandhuCommented:
Update your query that you posted. Please run to see if you are getting the correct results:
 

string strTestSQL = "SELECT [id],
							UPPER(Left([mmap],1))+LOWER(RIGHT([mmap],LEN([mmap])-1)), 
							UPPER(Left([mlnam],1))+LOWER(RIGHT([mlnam],LEN([mlnam])-1)), 
							UPPER(Left([mfnam],1))+LOWER(RIGHT([mfnam],LEN([mfnam])-1)),  
							UPPER(Left([madd1] + ', ' + [madd2] + ', ' +[mzip5],1))+LOWER(RIGHT([madd1] + ', ' + [madd2] + ', ' +[mzip5],LEN([madd1] + ', ' + [madd2] + ', ' +[mzip5])-1)) as mailingAddress, 
							UPPER(Left([macre#],1))+LOWER(RIGHT([macre#],LEN([macre#])-1)), 
							UPPER(Left([mhse#] + ' ' + [mstrt] +' ' + [msttyp] ,1))+LOWER(RIGHT([mhse#] + ' ' + [mstrt] +' ' + [msttyp] ,LEN([mhse#] + ' ' + [mstrt] +' ' + [msttyp] )-1)) as propertyAddress, 
							UPPER(Left([mdbook],1))+LOWER(RIGHT([mdbook],LEN([mdbook])-1)), 
							UPPER(Left([mdpage],1))+LOWER(RIGHT([mdpage],LEN([mdpage])-1)),
							UPPER(Left([rec#alph],1))+LOWER(RIGHT([rec#alph],LEN([rec#alph])-1)), 
							UPPER(Left([minno#],1))+LOWER(RIGHT([minno#],LEN([minno#])-1)),  
					FROM	[tblCamraGIS] 
					WHERE	[REC#ALPH] LIKE '%" + txtRECALPH.Text + "%'";

Open in new window

0
mrcoulsonAuthor Commented:
Okay!  Scorelli, angelIII, and shru_0409: Those seem to be operations on the database itself, correct?  I can't change the data in the database.  Well, I CAN, but I'd rather not because the data gets there in all caps and there may be some reason they want to keep it all in caps.

ITGuy64: I see what you're doing there, but I don't know how many words will be in each field.

pssandhu: If I use your final idea, I'm told there's an error "near the keyword FROM".  If I use your other ideas and add "AS [something]" to each one, it works sorta.  Since some of these fields have more than one word, it only capitalizes the first letter of each field.  For example, I want "Gilley, Victoria D", but I get "Gilley, victoria d".

So, how do I make this idea work on words instead of the entire field?

Jeremy




0
ITGuy64Commented:
Jeremy,
  Since you want it in either lower case or in proper/title case, I would go with the lower(...) AS ... command.  There is no easy way to do a complete name in proper/title case.  If the names (first/middle/last) were already separated, you can then do the concatenation that pssandhu describes.
  Sorry.
0
mrcoulsonAuthor Commented:
Yeah, I'm thinking I might need to tell these folks they'll need to either start entering things in proper case or expect to see it in their web application in caps.  This would be easier if each field followed some sort of pattern, but there will even be problems with some of them if I separate them into separate fields based on a space because of street names like North Kent Street.  Alas...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
there is a function to be created, but the data does not NEED to be updated.
you can use the function for SELECT or UPDATE queries .
0
pssandhuCommented:
I frogot to take out the comma after last field in the select, that is why you are getting the error. Take that out and the query should run fine.
And I agree, there is no easy way to do this. Proper way of handling would be a function but that will depend on how you data looks like. It is especially difficult if you try to propercase a text column because the data is so random.
P.
0
ScorelliCommented:
It was a long time ago, and my memory is foggy, but I remember one time I received a database where, due to the codepage used to create it, everything defaulted to upper. When I fixed it, and changed the code page, moving forward the data looked good. I "scrubbed" the existing data in excel and reloaded it.  
0
mrcoulsonAuthor Commented:
Carry on LearnedOne. This was abandoned because I had to abandon the project at work.

Jeremy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.