Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Detective Stories - Changing the Case


Detective Stories - Changing the Case

Author
Message
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12168 Visits: 8905
Comments posted to this topic are about the item Detective Stories - Changing the Case

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
mister.magoo
mister.magoo
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3468 Visits: 7861
There was a thread about this a while back where Paul White showed an excellent SQLCLR method http://www.sqlservercentral.com/Forums/FindPost910545.aspx

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • LutzM
    LutzM
    SSCrazy Eights
    SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

    Group: General Forum Members
    Points: 8991 Visits: 13559
    How about this?

    ;WITH cte AS
    -- split each string by character and decide UPPER or lower based on previous character or being the first char
    (
    SELECT
    N,
    Id,
    CASE
    WHEN N=1 OR (N>1 AND SUBSTRING(location ,N-1,1 ) = ' ')
    THEN UPPER(SUBSTRING(location ,N ,1))
    ELSE LOWER(SUBSTRING(location ,N ,1))
    END AS split
    FROM Tally
    CROSS APPLY -- apply the code to each location
    ( SELECT Location, min(MyId) AS Id
    FROM Import_Data_Filter
    GROUP BY Location
    )y
    WHERE N < LEN(' ' + location + ' ')
    )
    -- and put it back together
    SELECT
    REPLACE((SELECT '' + split FROM cte c2 WHERE c2.Id = c1.Id ORDER BY N FOR XML PATH('')),' ',' ')
    FROM cte c1
    GROUP BY Id





    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question
    How to post performance related questions
    Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
    robr-793239
    robr-793239
    SSC Rookie
    SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

    Group: General Forum Members
    Points: 46 Visits: 105
    I've read through the examples and find the variety of techniques interesting. When I had to solve the problem I was looking at fixing the data as it went into the database, so my solution is a generalized function that can be applied with an Insert or Update process. I offer it as another solution if anyone finds it of interest.


    CREATE FUNCTION [dbo].[FormalCase]
    (
    @Input varchar(255)
    )
    RETURNS varchar(255)
    AS
    BEGIN
    DECLARE @Results varchar(255)

    if len(@Input)>0
    Begin
    Set @Input = lower(ltrim(rtrim(@Input)))
    Declare @NextSpace int, @LastSpace int
    Set @LastSpace = 0
    Set @NextSpace = charindex(char(32),@Input,1)

    While @NextSpace>@LastSpace
    Begin
    Set @Input = Left(@Input, @NextSpace) + upper(substring(@Input,@NextSpace + 1, 1)) + Right(@Input, len(@Input)-(@Nextspace+1))
    Set @LastSpace = @NextSpace
    Set @NextSpace = charindex(char(32),@Input,@LastSpace + 1)
    End

    SELECT @Results = Upper(left(@Input,1)) + right(@Input, len(@Input)-1)
    End
    Else
    Set @Results= ''

    RETURN @Results
    END
    /* UNIT TESTING
    Select dbo.formalcase('a stitch in time')
    */
    GO


    arty 15255
    arty 15255
    SSC Rookie
    SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

    Group: General Forum Members
    Points: 43 Visits: 77
    my solution:


    Create Table #Import_Data_Filter (MyID Int Identity(1,1) NOT NULL, Location varchar(100))

    Insert into #Import_Data_Filter (Location)
    (Select Lower('Mandarin') UNION ALL Select Lower('San Jose') UNION ALL Select Lower('Baymeadows') UNION ALL
    Select Lower('My FH Locale') UNION ALL Select Lower('St. Augustine') UNION ALL Select Lower('Test For Three Spaces')
    UNION ALL Select Lower('Test for being Four Spaces') UNION ALL Select Lower('Test for being Five More Spaces')
    UNION ALL Select Lower('Baymeadows') UNION ALL Select Lower('St. Augustine'))

    update #Import_Data_Filter set
    location = char(160)+replace(location,' ',char(160))

    r:
    update #import_data_filter set
    location = replace(
    location,
    char(160)+substring(location,charindex(char(160),location)+1,1),
    ' '+UPPER(substring(location,charindex(char(160),location)+1,1))
    )
    where charindex(char(160),location)<>0
    if @@rowcount>0 goto r

    select ltrim(location) from #Import_Data_Filter

    drop table #Import_Data_Filter



    p.s.: You can change char(160) to anything.
    sjones
    sjones
    Forum Newbie
    Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

    Group: General Forum Members
    Points: 6 Visits: 103
    I inherited this function which uses regular expressions to look for where a letter should be capitalised. In the current incarnation, it looks for the pattern, space, comma, apostrophe (uses '', because the apostrophe will be embedded in a string, followed by a letter.

    ALTER FUNCTION [dbo].[fnProperCase] 


    (
    @String varchar(1023)
    )

    RETURNS varchar(1023)

    AS

    BEGIN

    DECLARE @Pos int,
    @Temp varchar(1023),
    @Result varchar(1023)

    SET @Temp = LOWER(LTRIM(RTRIM(@String)))
    SET @Result = UPPER(SUBSTRING(@Temp,1,1))
    SET @Temp = SUBSTRING(@Temp,2,1022)
    SET @Pos = PATINDEX('%[ ,''-][a-z]%', @Temp)
    WHILE @Pos > 0
    BEGIN
    SET @Result = @Result + SUBSTRING(@Temp,1,@Pos) + UPPER(SUBSTRING(@Temp,@Pos+1,1))
    SET @Temp = SUBSTRING(@Temp, @Pos + 2, 1022)
    SET @Pos = PATINDEX('%[ ,''-][a-z]%', @Temp)
    END
    SET @Result = @Result + @Temp

    RETURN @Result

    END





    Jan Van der Eecken
    Jan Van der Eecken
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2329 Visits: 6492
    Hi Brandie,

    I guess the final update back will fail on a database with a case sensitive collation. Shouldn't it rather read:

    Update idf
    Set Location = mt1.Location
    from dbo.Import_Data_Filter idf
    join dbo.#MyTemp1 mt1
    on UPPER(idf.Location) = UPPER(mt1.Location);



    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)

    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
    ronmoses
    ronmoses
    Ten Centuries
    Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

    Group: General Forum Members
    Points: 1153 Visits: 996
    I'll throw in my proper case function just for laughs...


    ALTER FUNCTION [cf_ProperCase] (@String varchar(MAX))
    RETURNS varchar(MAX)
    AS BEGIN

    DECLARE @ReturnString varchar(max),
    @i int;

    SET @ReturnString = ''
    SET @i = 1

    WHILE @i <= LEN(@String)
    BEGIN
    SET @ReturnString = @ReturnString +
    (CASE WHEN (@i = 1 OR SUBSTRING(@String, @i - 1, 1) = ' ')
    THEN UPPER(SUBSTRING(@String, @i, 1))
    ELSE LOWER(SUBSTRING(@String, @i, 1))
    END)
    SET @i = @i + 1
    END

    RETURN @ReturnString


    END


    Very brute-force, but it works.

    Ron Moses

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

    mrpolecat
    mrpolecat
    Mr or Mrs. 500
    Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)

    Group: General Forum Members
    Points: 518 Visits: 856
    I think I originally got this from Steve Jones or at least one of his posts. Made a couple of tweaks to handle Roman Numerals because we mostly use it for names.



    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    ALTER procedure sp_ProperCase
    @table varchar(50),@field varchar(50)
    as

    -- Replace spaces with the "special" character
    exec('update ' + @table + '
    set ' + @field + ' = lower(replace( ' + @field + ' , '' '', ''@''))')


    -- Handle case 1 - First item
    exec('update ' + @table + '


    set ' + @field + ' = upper( substring( ltrim( ' + @field + ' ), 1, 1)) + substring( ltrim( ' + @field + ' ), 2, 80)')

    -- loop while there are rows with the flag
    exec('while exists(
    select *
    from ' + @table + '
    where ' + @field + ' like ''%@%''
    )
    begin
    -- Proper case the word after the flag.
    update ' + @table + '
    set ' + @field + ' = substring( ' + @field + ' , 1, charindex( ''@'', ' + @field + ' )) +
    upper( substring( ' + @field + ' , charindex( ''@'', ' + @field + ' )+1, 1 )) +
    substring( ' + @field + ' , charindex( ''@'', ' + @field + ' )+2, 80)
    where ' + @field + ' like ''%@%''
    -- Remove the first flag encountered in each row
    update ' + @table + '
    set ' + @field + ' = substring( ' + @field + ' , 1, charindex( ''@'', ' + @field + ' )-1) +
    '' '' + substring( ' + @field + ' , charindex( ''@'', ' + @field + ' ) + 1, 80)
    where ' + @field + ' like ''%@%''
    end')

    exec('update ' + @table + ' set ' + @field + ' = replace(' + @field + ','' ii'','' II'') where ' + @field + ' like ''% ii''')
    exec('update ' + @table + ' set ' + @field + ' = replace(' + @field + ','' iii'','' III'') where ' + @field + ' like ''% iii''')
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO








    sdsiedentop
    sdsiedentop
    Forum Newbie
    Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

    Group: General Forum Members
    Points: 7 Visits: 22
    Back in the "olden days" when I did FoxPro development, I used a function called PROPER() quite frequently. I'm not sure why the SQL development team didn't include such a function in their product, but I digress...

    I've come up with a number of ways over the years to replicate the FoxPro PROPER() function in SQL and it makes me happy to see that I'm not the only one that needed such functionality.

    SQL is great and I still love FoxPro. In fact, I can do anything in FoxPro....except get a job.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search