uses of string splitters

  • ok, thank you very much for spending time with me on this question today, 'delicious' and Sean.

  • Delicious, I am totally impressed. I'm going to spend some time thinking about your example. Thanks for providing details.

    I would have been proud too. Nice nice nice!

  • KoldCoffee (5/29/2014)


    in the book SQL Server Common Table Expressions Joes 2 Pros by Steve Stedman et al., I read about the use of splitters to count the number of times a certain word appears in a string. I thought that was a novel use of the splitter and would be interested in more examples like that...

    For this type of thing I would do something more like this.

    declare @string varchar(max) = 'Lorem ipsum dolor sit foo amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt foo ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo foo consequat. Duis aute irure dolor in reprehenderit in voluptate foo velit esse cillum dolore eu foo fugiat nulla pariatur. Excepteur sint foo occaecat cupidatat foo non proident, sunt in culpa qui officia deserunt mollit foo anim id est laborum.'

    declare @StringToFind varchar(50) = 'foo'

    select (LEN(@string) - len(REPLACE(@string, ' ' + @StringToFind + ' ' , ''))) / (LEN(@StringToFind) + 2) as NumOccurences

    For @string I just used the standard lorem ipsum and added the word "foo" in there 8 times. There really is no need to go to the trouble of splitting this on spaces for something like that. 😛

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Here is an example of a real world case for using a splitter. This iTVF will return a string with the proper case. I did not write this and sadly my notes don't have who posted this. I know it came from this site at one point.

    CREATE FUNCTION [dbo].[ProperCaseWithNumbers]

    (

    @StrIn VARCHAR(255)

    )

    RETURNS TABLE WITH SCHEMABINDING

    AS

    RETURN

    SELECT STUFF((

    SELECT CASE

    WHEN split.Item LIKE '%[0-9]%'

    THEN ' ' + split.Item

    ELSE ' ' + Upper(LEFT(split.Item, 1)) + Lower(Substring(split.Item, 2, 255))

    END AS word

    FROM dbo.Delimitedsplit8k(@StrIn, ' ') split

    FOR XML PATH('')

    ,TYPE

    ).value('.', 'varchar(255)'), 1, 1, '') AS ProperCased

    GO

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • OK I'm taking a look at Delicious's first. So, basically you have the equivalent of a build-a-bear business, with nearly limitless variations available though the combination and recombination of a limited set of options. The reporting limitation on front end was overcome by your getting your hands on the flat file and importing it to a table. Reason for your using a 'temp table' less important, but ultimately it sounds like you gave your friend a TVF that she could pass an attribute to (as a parameter) and get returned a top 10 or top 100 table.

    Great! Thanks for the real life example.

  • Sean Lange (5/29/2014)


    Here is an example of a real world case for using a splitter. This iTVF will return a string with the proper case. I did not write this and sadly my notes don't have who posted this. I know it came from this site at one point.

    I wonder why it was easy for me to remember that post. 😀

    Original thread of the code

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (5/29/2014)


    Sean Lange (5/29/2014)


    Here is an example of a real world case for using a splitter. This iTVF will return a string with the proper case. I did not write this and sadly my notes don't have who posted this. I know it came from this site at one point.

    I wonder why it was easy for me to remember that post. 😀

    Original thread of the code

    Fun thread

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I know that STUFF takes 3 parameters: (the string, the starting position, the number of characters to delete) but I can't break Luis's TVF into those three. Can you format it a little bit so I can see how STUFF is being applied?

    edit: and could you elaborate just a little bit on "return a string with the proper case" ... what was purpose?

  • KoldCoffee (5/29/2014)


    I know that STUFF takes 3 parameters: (the string, the starting position, the number of characters to delete) but I can't break Luis's TVF into those three. Can you format it a little bit so I can see how STUFF is being applied?

    That's because it takes 4 ;-).

    I could explain it, but Wayne S has already done a great job in this article. http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • it's a no good link

  • KoldCoffee (5/29/2014)


    it's a no good link

    link works for me

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (5/29/2014)


    KoldCoffee (5/29/2014)


    it's a no good link

    link works for me

    It might have been a problem with the site. It seems to be intermitence due to maintenance.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • OK, the link works for me now. The example there is formatted exactly same way as here and wondering if you could put each of the four STUFF parameters on separate lines so it's clear what part of the string is which.

  • Something like this?

    CREATE FUNCTION [dbo].[ProperCaseWithNumbers]

    (

    @StrIn VARCHAR(255)

    )

    RETURNS TABLE WITH SCHEMABINDING

    AS

    RETURN

    SELECT STUFF(

    ----Start of first parameter

    (SELECT CASE

    WHEN split.Item LIKE '%[0-9]%'

    THEN ' ' + split.Item

    ELSE ' ' + Upper(LEFT(split.Item, 1)) + Lower(Substring(split.Item, 2, 255))

    END AS word

    FROM dbo.Delimitedsplit8k(@StrIn, ' ') split

    FOR XML PATH('')

    ,TYPE

    ).value('.', 'varchar(255)') ----End of first parameter. List generated as: ',item1,item2,item3,...,itemN'

    , 1 ----Position (first character)

    , 1 ----Number of characters to be replaced (one, the first comma)

    , '' ----Replacement string (empty)

    ) AS ProperCased

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • thank you Luis! that is so nice of you. Got it and reading other post.

Viewing 15 posts - 16 through 30 (of 59 total)

You must be logged in to reply to this topic. Login to reply