May 29, 2014 at 2:42 pm
ok, thank you very much for spending time with me on this question today, 'delicious' and Sean.
May 29, 2014 at 2:51 pm
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!
May 29, 2014 at 2:51 pm
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/
May 29, 2014 at 2:58 pm
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/
May 29, 2014 at 3:14 pm
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.
May 29, 2014 at 3:23 pm
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. 😀
May 29, 2014 at 3:39 pm
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. 😀
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
May 29, 2014 at 3:44 pm
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?
May 29, 2014 at 3:47 pm
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/
May 29, 2014 at 4:00 pm
it's a no good link
May 29, 2014 at 4:23 pm
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
May 29, 2014 at 4:36 pm
SQLRNNR (5/29/2014)
KoldCoffee (5/29/2014)
it's a no good linklink works for me
It might have been a problem with the site. It seems to be intermitence due to maintenance.
May 29, 2014 at 5:01 pm
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.
May 29, 2014 at 5:07 pm
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
May 29, 2014 at 6:56 pm
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