Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Detective Stories - Changing the Case Expand / Collapse
Author
Message
Posted Saturday, October 16, 2010 4:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 8:19 AM
Points: 7,002, Visits: 6,181
Comments posted to this topic are about the item Detective Stories - Changing the Case

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1005794
Posted Saturday, October 16, 2010 5:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 1,651, Visits: 5,193
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1005797
    Posted Saturday, October 16, 2010 5:30 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Tuesday, April 01, 2014 9:38 AM
    Points: 6,908, Visits: 12,624
    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
    Post #1005800
    Posted Sunday, October 17, 2010 10:58 PM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Monday, March 31, 2014 5:13 PM
    Points: 21, Visits: 88
    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

    Post #1005998
    Posted Monday, October 18, 2010 1:46 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Thursday, October 24, 2013 3:46 AM
    Points: 15, Visits: 67
    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.
    Post #1006025
    Posted Monday, October 18, 2010 3:12 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Wednesday, February 22, 2012 3:22 AM
    Points: 4, 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




    Post #1006053
    Posted Monday, October 18, 2010 6:13 AM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Monday, March 17, 2014 7:18 AM
    Points: 2,439, Visits: 6,426
    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)
    Post #1006154
    Posted Monday, October 18, 2010 6:19 AM


    SSC Eights!

    SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

    Group: General Forum Members
    Last Login: 2 days ago @ 10:58 AM
    Points: 819, Visits: 832
    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
    Post #1006157
    Posted Monday, October 18, 2010 7:24 AM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Wednesday, May 01, 2013 8:10 AM
    Points: 445, Visits: 840
    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








    Post #1006200
    Posted Monday, October 18, 2010 8:06 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Sunday, October 14, 2012 1:07 PM
    Points: 5, 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.

    Post #1006241
    « Prev Topic | Next Topic »

    Add to briefcase 1234»»»

    Permissions Expand / Collapse