Solved

How can I change the case of my SQL result?

Posted on 2009-07-08
16
420 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 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 143

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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
 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
disk usage reporting tools 27 51
Upgrading to SQL Server 2015 Express 2 30
SQL Server Resume 5 44
SQL Server Express or Standard? 5 24
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

762 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