Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How can I change the case of my SQL result?

Posted on 2009-07-08
16
Medium Priority
?
433 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +3
16 Comments
 
LVL 17

Accepted Solution

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

Assisted Solution

by:Scorelli
Scorelli earned 500 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 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
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 1000 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
 
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 143

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 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