Capital letters - SQL Server Forums

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

Capital letters

Author

Topic

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-09-11 : 13:37:39

Hello,

I having some trouble to display the name correct in a listbox.

The code I use is the following:

LEFT(UPPER(Werknemers.Achternaam),1) + LOWER(RIGHT(Werknemers.Achternaam,LEN(Werknemers.Achternaam)-1))

For the name blanckaert the result is correct like Blanckaert.
But when the name is like 2 pieces it isn't correct.
For example van den broeck becomes Van den broeck and it should be
Van Den Broeck.

How can I change the code so that after every gap the first letter is a capital letter?

Thanks in advance,
Sven.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 13:59:42

Here are some solutions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10107

and these:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56499
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121886

Be One with the Optimizer
TG

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-09-11 : 16:12:36

Hello,

I have tried it like this but it doesn't work ...

Left(Werknemers.achternaam, charindex(' ', werknemers.achternaam) - 1)
Mid(UCase(Werknemers.achternaam), charinde(' ',Werknemers.achternaam, 1), 2 "
LCase(Mid(Werknemers.achternaam, charindex(' ',Werknemers.achternaam, 1) + 2, Len(Werknemers.achternaam)))

What is wrong with this code?

Sven.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 16:20:46

Mid and LCase are not MS Sql Server functions. (I think they are VBScript)

Are you trying to do this in MS Sql Server? If so have you looked through the links I posted?

Be One with the Optimizer
TG

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-09-11 : 16:24:48

Hello,

I tried to figure out the topics (links) you posted but I really don't understand them as I am a beginner in SQL.

What I am trying to do is send a SQL string to a SQL server 2005.

Sven.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 16:52:37

Ok, here is a little function that should work for you. Try running this code and see if it works for you.



-----------------------------------------------------------
--Here is code to create the function

if object_id('dbo.initCap') > 0
       drop function dbo.initCap
go
create function dbo.initCap(@s nvarchar(200))
returns nVarchar(200)
as
begin
       declare @i int
       set @s = upper(left(@s, 1)) + lower(substring(@s, 2, len(@s)-1))

       select @i = charindex(' ', @s)
       while @i > 0 and @i < len(@s)
       begin
              set @s = stuff(@s, @i+1, 1, upper(substring(@s, @i+1, 1)))
              select @i = charindex(' ', @s, @i+1)
       end

       select @i = charindex('''', @s)
       while @i > 0 and @i < len(@s)
       begin
              set @s = stuff(@s, @i+1, 1, upper(substring(@s, @i+1, 1)))
              select @i = charindex('''', @s, @i+1)
       end

       return @s
end
go

-----------------------------------------------------------

--Run this to test the code

select dbo.initCap(Achternaam)

from   (--This is your table

       select 'van den broeck' as Achternaam union all
       select 'john doe' union all
       select 'cher' union all
       select 'paddy o''brien' union all
       select 'mary beth butt'

       ) as Werknemers



Here is the output from the test:
Van Den Broeck
John Doe
Cher
Paddy O'Brien
Mary Beth Butt

Be One with the Optimizer
TG

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-09-12 : 04:14:21

Hello,

I have created the function on the server and it works just fine.
Thanks for the help.

Best regards,
Sven.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-13 : 12:22:29

Glad you got it working - you're welcome

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-14 : 02:19:41

Where do you want to show data?
If you use VB, you can make use of vbProperCase function

Madhivanan

Failing to plan is Planning to fail

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-09-14 : 14:23:56

Hello,

I have 2 computers with my database on it. On 1 computer I get the procedure Initcap saved. On my other computer I open the function hit execute and get the message commands complete successfully. But when I take a look under functions the function initcap isn't there.

What can be the reason that the function isn't saved or created?

Sven.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 14:31:20

quote:
Originally posted by sven2

Hello,

I have 2 computers with my database on it. On 1 computer I get the procedure Initcap saved. On my other computer I open the function hit execute and get the message commands complete successfully. But when I take a look under functions the function initcap isn't there.

What can be the reason that the function isn't saved or created?

Sven.

if they are two different dbs your changes wont get reflected unless you've manually transfer function b/w them or using export/import wizard. you need to create function separately in each

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-14 : 14:54:58

quote:

But when I take a look under functions the function initcap isn't there.

What can be the reason that the function isn't saved or created?

Another possibility could be if when you "look" for the function when you are connected to the server as a user which is not a DBO. Since the function was created as dbo.InitCap non db owners may not have permission to see it.

Be One with the Optimizer
TG

sven2
Yak Posting Veteran

57 Posts

Posted - 2009-09-14 : 15:35:42

Hello,

that could be the case. I am logged on as a systemadministrator and not as a databaseowner.

Sven.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-14 : 16:07:22

well, if you're connected as sa then you should be able to see everything. Do you still have the window open where you compiled the function? Perhaps you compiled it in a different database... Or perhaps you simply need to "right-click | refresh" the Functions folder.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources