Setting 1st character of each word to upper case

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Setting 1st character of each word to upper case

Author

Topic

Oliver_Bradley
Starting Member

6 Posts

Posted - 2012-03-21 : 07:35:00

I'm selecting multiple fields (30+) in SQL Server 2008 and certain fields need to be returned in a specified format.

The case in point is Address. The field holds the house number and road name (102 grange road for example).

I need the results to be returned with the first letter of each word in upper case (102 Grange Road).

I'm aware that using the UPPER and LOWER functions can be used to replicate to Oracle's INITCAP function with the help of substring but i'm having trouble with the syntax. Here's where i'm up to;

upper(Left(ad.address1, 1)) + lower(substring(ad.address1, 2, (len(ad.address1)))),

This isn't changing anything. I'm guessing the fact that the field begins with numbers is why i'm having such trouble.

Can anyone think of an easier way of doing this without having to create a UDF?

Apologies if i've not explained everything correctly, relatively new to Database Deveopment!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-21 : 08:36:42

Take a look at Madhivanan's blog here to see if that would help you: http://beyondrelational.com/modules/2/blogs/70/posts/10901/tsql-initcap-function-convert-a-string-to-proper-case.aspx
And also the link he has to SQL Authority blog.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 10:09:05

why should you do it in db? isnt it presntation issue which is best handled at your front end application?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Oliver_Bradley
Starting Member

6 Posts

Posted - 2012-03-21 : 10:27:28

Thanks for the replies, much appreciated!

Thanks for the link. I've actually already come across that blgog but something like that won't be practical for this particular project unfortunately as this problem is a small part of a long select statement.

The people dealing with the front end application are spending too much time title casing names/addresses, putting spaces in the middle of postcodes etc. so my solution is to create the code, create a report in SSRS, then the configured results can be exported as necessary. I've figured every other problem out, it's just casing the addresses.

Is there a way of getting my code to find the spaces(' ') and then Upper casing the 1st letter after every space? Seems like that would be the most logical solution. However again i'm relatively new to DB Development. Any help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 10:29:31

so my solution is to create the code, create a report in SSRS, then the configured results can be exported as necessary.

then why not do this in reporting services? you've formatting functions available in it too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Oliver_Bradley
Starting Member

6 Posts

Posted - 2012-03-21 : 10:40:38

quote:
Originally posted by visakh16

so my solution is to create the code, create a report in SSRS, then the configured results can be exported as necessary.

then why not do this in reporting services? you've formatting functions available in it too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Like I said, i'm relatively new to this entire field of work (8 weeks). That's brilliant though, i'll get researching on how to do it in SSRS.

Thanks, massive help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 10:46:22

try

=StrConv(Fields!YourFieldName.Value, vbProperCase)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-03-21 : 10:46:49

<<
Is there a way of getting my code to find the spaces(' ') and then Upper casing the 1st letter after every space? Seems like that would be the most logical solution. However again i'm relatively new to DB Development. Any help would be appreciated.
>>

My blog post exactly does this. Have you seen it?

Madhivanan

Failing to plan is Planning to fail

Oliver_Bradley
Starting Member

6 Posts

Posted - 2012-03-21 : 11:38:37

I've had a look and I can see how it's going to work. However I have no clue as to how to incorporate it using a field (in my case address1) rather than the variable strings that you have used. Is that possible?

Thanks for all of the replies thus far. Every day is a school day and all that!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 12:14:01

you need to create function using logic Madhi posted and then call it from your sql code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Oliver_Bradley
Starting Member

6 Posts

Posted - 2012-03-21 : 12:43:27

quote:
Originally posted by visakh16

try

=StrConv(Fields!YourFieldName.Value, vbProperCase)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This worked perfectly. Thanks very much!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-03-22 : 01:27:14

quote:
Originally posted by Oliver_Bradley

quote:
Originally posted by visakh16

try

=StrConv(Fields!YourFieldName.Value, vbProperCase)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This worked perfectly. Thanks very much!

So are you using SSRS? You must have posted in SSRS specific forum

Madhivanan

Failing to plan is Planning to fail

Oliver_Bradley
Starting Member

6 Posts

Posted - 2012-03-22 : 06:28:05

Apologies. I would have done if I'd have known the solution was in SSRS to begin with.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 15:18:25

quote:
Originally posted by Oliver_Bradley

Apologies. I would have done if I'd have known the solution was in SSRS to begin with.

The recommended approach is do it in SSRS as this is a case of formatting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources