INITCAP Function | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

INITCAP Function

hi
I would like to convert All Capital Words into Init Cap
ex : ‘FUNCTION FN_INITCAP’ into ‘Function Fn_initcap’ is there any function on sql2k to do that .. best regrads,
Do you mean you want to rename all your stored procedures, the text of the stored procedures, functions and text of the function at once? Frank
http://www.insidesql.de
http://www.familienzirkus.de
no I want to use it on select statement SELECT Name,addr=fn_initcap(address) from FLATFILE08
If you want to display these data in some kind of report I suggest you do this at the client, not at the server. Should be an easy exercise for front-end tools. Frank
http://www.insidesql.de
http://www.familienzirkus.de
Since i can find it at oracle INITCAP, i would do the same thing at SQL2K. I think I need to create UDF, but since UDF can’t use Temporary Table, I’ve no idea how to create this such as function.
FOund this code from Developers Dex site, contributed by
Author: Nagabhushanam
——-
CREATE function INITCAP (@inString varchar(4000) )
/* INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric */
returns varchar(4000)
as
BEGIN
DECLARE @i int, @c char(1),@result varchar(255)
SET @result=LOWER(@inString)
SET @i=2
SET @result=STUFF(@result,1,1,UPPER(SUBSTRING(@inString,1,1)))
WHILE @i<=LEN(@inString)
BEGIN
SET @c=SUBSTRING(@inString,@i,1)
IF (@c=’ ‘) OR (@c=’;’) OR (@c=’:’) OR (@c=’!’) OR (@c=’?’) OR (@c=’,’)OR (@c=’.’)OR (@c=’_’)
IF @i<LEN(@inString)
BEGIN
SET @[email protected]+1
SET @result=STUFF(@result,@i,1,UPPER(SUBSTRING(@inString,@i,1)))
END
SET @[email protected]+1
END
RETURN @result
END
———- _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Not everything that can be done in Oracle is good [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />However, since you are on SQL2k you can use a table variable.<br /><br />What I meant, was that you are required to loop through your char column and that’s one thing SQL Server is not really good at, I mean this looping is more easily done within your programming language<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
quote:Originally posted by satya FOund this code from Developers Dex site, contributed by
Author: Nagabhushanam
——-
CREATE function INITCAP (@inString varchar(4000) )
/* INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric */
returns varchar(4000)
as
BEGIN
DECLARE @i int, @c char(1),@result varchar(255)
SET @result=LOWER(@inString)
SET @i=2
SET @result=STUFF(@result,1,1,UPPER(SUBSTRING(@inString,1,1)))
WHILE @i<=LEN(@inString)
BEGIN
SET @c=SUBSTRING(@inString,@i,1)
IF (@c=’ ‘) OR (@c=’;’) OR (@c=’:’) OR (@c=’!’) OR (@c=’?’) OR (@c=’,’)OR (@c=’.’)OR (@c=’_’)
IF @i<LEN(@inString)
BEGIN
SET @[email protected]+1
SET @result=STUFF(@result,@i,1,UPPER(SUBSTRING(@inString,@i,1)))
END
SET @[email protected]+1
END
RETURN @result
END
———-

hm….that’s what I meant. Will surely work, but turns it into row by row processing.
And it’s really more of a presentational issue and as such job of the front end.
Frank
http://www.insidesql.de
http://www.familienzirkus.de
You must find out the ways to do it and lets hope Originator feels this code will help. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Hey, consider this just my $0.02 cents [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
I have to agree with Frank. It it something that should be done on client side. Bambola.

I’ve done this. I created a UDF similar to the one below (although I called it something like propercase). It runs very fast and can be called from many processes. While this can also easily be done on the client side, I needed it as part of a data scrubbing routine to put the data into a CRM from a legacy system (not sql, heck not relational) that required the names to be upper case. Chris

client side would be the simplest option, but If you needed extra speed you could also create a tiny dll in VB using its StrConv( str, VbStrConv.ProperCase ), then register that dll as an external stored procedure Cheers
Twan
quote:Originally posted by Twan
client side would be the simplest option, but If you needed extra speed you could also create a tiny dll in VB using its StrConv( str, VbStrConv.ProperCase ), then register that dll as an external stored procedure Cheers
Twan
external stored procedure or extended stored procedure? You can’t write extended stored procedures in VB, because VB doesn’t create true Win32 executables and it doesn’t allow you to export functions like C++ or C.
However, you can write the Dll in VB and instantiate it via the sp_OA* stored procedures. Frank
http://www.insidesql.de
http://www.familienzirkus.de
Ah sorry Frank I meant extended, and I wasn’t aware that VB.NET wouldn’t generate true dlls…? Twan
Sorry, should have mentioned that I don’t have experience with .Net.
I’ll take that road next year. Can you do this with VB.Net?
Hm…if so I’m a little bit scared what happens when you can programm Yukon with it. Frank
http://www.insidesql.de
http://www.familienzirkus.de
<br />Hi Frank,<br /><br />I’m not a real VB.NET programmer either, but I was under the impression that it does allow you to create true dlls…<br /><br />yep Yukon should be a lucrative proposition for performance/architecture consultants <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Cheers<br />Twan
Try something like
__declspec(dllexport) ULONG __GetXpVersion()
{
return ODS_VERSION;
}
taken from BOL I’m pretty sure when trying to call a VB Dll function, you’ll receive something like "Cannot find the function blabla in the library blabla.dll. Reason: 127(The specified procedure could not be found.)." because even if there is an entry point found like DllMain, the function itself will not be found. Don’t get me wrong, I would like to be mistaken, because it would potentially make things easier on the one side if VB would work, on the other side I think it’s ok that only C or C++ could be used. Why? Because poorly programmed extended stored procedures (no matter what language) are likely to crash SQL Server, as is mentioned here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_9rxv.asp Here are some more useful links on this http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_3fhq.asp
http://search.microsoft.com/search/results.aspx?View=msdn&st=a&s=2&c=4&qu=extended stored procedures I hope you’re right on Yukon, because I’ll bet you’ll come across many SQL Server CLR stored procedures containing
Do While Not recordset.EOF
..
Loop
I wonder how this is consistent with set-based theory??? Just my $0.02 cents. Frank
http://www.insidesql.de
http://www.familienzirkus.de
]]>