Solved

How can I change the case of my SQL result?

Posted on 2009-07-08
16
408 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:mrcoulson
  • 4
  • 4
  • 2
  • +3
16 Comments
 
LVL 17

Accepted Solution

by:
pssandhu earned 250 total points
ID: 24804066
You cans use the LOWER() function:
Eg, Select LOWER(FieldName) From Table
P.
0
 

Assisted Solution

by:Scorelli
Scorelli earned 125 total points
ID: 24804067
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 24804078
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
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 250 total points
ID: 24804123
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
 
LVL 14

Expert Comment

by:shru_0409
ID: 24804231
CREATE FUNCTION InitCap (
 @string varchar(255)
)  
RETURNS varchar(255) AS
BEGIN
 RETURN upper(left(@string, 1)) + right(@string, len(@string) - 1)
END
0
 

Expert Comment

by:ITGuy64
ID: 24804285
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
 

Author Comment

by:mrcoulson
ID: 24804302
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Expert Comment

by:pssandhu
ID: 24804486
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
 

Author Comment

by:mrcoulson
ID: 24806592
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
 

Expert Comment

by:ITGuy64
ID: 24806688
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
 

Author Comment

by:mrcoulson
ID: 24806716
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24806789
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24807530
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
 

Expert Comment

by:Scorelli
ID: 24995777
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
 

Author Comment

by:mrcoulson
ID: 24997852
Carry on LearnedOne. This was abandoned because I had to abandon the project at work.

Jeremy
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now