SQL Server Performance

INITCAP Function

Discussion in 'General DBA Questions' started by bluewave, Nov 3, 2003.

  1. bluewave New Member

    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,
  2. FrankKalis Moderator

  3. bluewave New Member

    no I want to use it on select statement

    SELECT Name,addr=fn_initcap(address) from FLATFILE08
  4. FrankKalis Moderator

  5. bluewave New Member

    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.



  6. satya Moderator

    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 @i=@i+1
    SET @result=STUFF(@result,@i,1,UPPER(SUBSTRING(@inString,@i,1)))
    END
    SET @i=@i+1
    END
    RETURN @result
    END
    ----------

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. FrankKalis Moderator

    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>
  8. FrankKalis Moderator

    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 @i=@i+1
    SET @result=STUFF(@result,@i,1,UPPER(SUBSTRING(@inString,@i,1)))
    END
    SET @i=@i+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
  9. satya Moderator

    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
  10. FrankKalis Moderator

    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>
  11. bambola New Member

    I have to agree with Frank. It it something that should be done on client side.

    Bambola.
  12. ChrisFretwell New Member

    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
  13. Twan New Member


    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
  14. FrankKalis Moderator

    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
  15. Twan New Member

    Ah sorry Frank I meant extended, and I wasn't aware that VB.NET wouldn't generate true dlls...?

    Twan
  16. FrankKalis Moderator

    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
  17. Twan New Member

    <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
  18. FrankKalis Moderator

    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

Share This Page