May 30, 2014 at 12:03 pm
Yes, I know about what you're talking about. I was lucky to once have a great manager tell me what an awesome job I did after I banged my head against a problem and gave him something he could use to make daily improvements. When he did that I thought, 'I am so awesome' (I did) and then I also told myself that it was child's play for the likes of Jeff Moden, Luis, Sean Lange....:-) But, the most important thing is you solved the problem the right way with the proper motivations and that's a story that repeated often enough eventually makes you like an expert and human being.
Suprrrr! Yup, whether or not you ever post those scripts you can learn from them. ps. your friend just takes it for granted that you are awesome. She probably never thought otherwise and it would be too obvious to say.
May 30, 2014 at 12:24 pm
KoldCoffee (5/30/2014)
Yes, I know about what you're talking about. I was lucky to once have a great manager tell me what an awesome job I did after I banged my head against a problem and gave him something he could use to make daily improvements. When he did that I thought, 'I am so awesome' (I did) and then I also told myself that it was child's play for the likes of Jeff Moden, Luis, Sean Lange....:-) But, the most important thing is you solved the problem the right way with the proper motivations and that's a story that repeated often enough eventually makes you like an expert and human being.Suprrrr! Yup, whether or not you ever post those scripts you can learn from them. ps. your friend just takes it for granted that you are awesome. She probably never thought otherwise and it would be too obvious to say.
Not sure I deserved to be grouped with those other two but thanks! Trust me there was a time I would have banged my head against the wall for something like that myself. In fact it wasn't all that long ago. It just takes practices and the willingness to share your ideas and learn from others.
I too would like to see what you came up with if you find them.
_______________________________________________________________
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 30, 2014 at 12:31 pm
Sean Lange (5/30/2014)
KoldCoffee (5/30/2014)
Yes, I know about what you're talking about. I was lucky to once have a great manager tell me what an awesome job I did after I banged my head against a problem and gave him something he could use to make daily improvements. When he did that I thought, 'I am so awesome' (I did) and then I also told myself that it was child's play for the likes of Jeff Moden, Luis, Sean Lange....:-) But, the most important thing is you solved the problem the right way with the proper motivations and that's a story that repeated often enough eventually makes you like an expert and human being.Suprrrr! Yup, whether or not you ever post those scripts you can learn from them. ps. your friend just takes it for granted that you are awesome. She probably never thought otherwise and it would be too obvious to say.
Not sure I deserved to be grouped with those other two but thanks!
That was exactly what I was thinking. 🙂
May 30, 2014 at 1:52 pm
I also have the Proper Case thread saved in my briefcase because it's good. Luis, great approach. BTW, I still have your Start With...Connect By solution in my briefcase too. 😉
I've used the splitter for many different things, including passing an unknown number of IDs from a client application as a string to a procedure. Once there, simply split them by the delimiter and they're in a usable table format. A neat side affect is that you don't have to use dynamic SQL in the procedure, so you injection-proof the procedure. Other uses include extracting a filename from a table of full paths and filenames and of course splitting up incoming data.
As for the tally table, I've heard it described as the "Swiss Army Knife of SQL" before. While it won't solve all the world's problems, it sure does a good job at what it's designed to do. It just a simple table of numbers, yet it has so many uses and it's screaming fast. You've probably already seen Jeff's original article, but in case you haven't, it is at http://www.sqlservercentral.com/articles/Tally+Table/72993/.
May 30, 2014 at 2:13 pm
Ed Wagner (5/30/2014)
I also have the Proper Case thread saved in my briefcase because it's good. Luis, great approach. BTW, I still have your Start With...Connect By solution in my briefcase too. 😉
I feel really honored :blush:
May 31, 2014 at 1:38 pm
I went to Tally Table site http://www.sqlservercentral.com/articles/T-SQL/62867/ folks here recommended. Would love clarification on how a loop for extracting the strings between a delimiter works, using his example.
--=============================================================================
-- Setup
--=============================================================================
USE TempDB --DB that everyone has where we can cause no harm
SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime DATETIME --Timer to measure total duration
SET @StartTime = GETDATE() --Start the timer
--=============================================================================
-- Create and populate a Tally table
--=============================================================================
--===== Conditionally drop and create the table/Primary Key
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
CREATE TABLE dbo.Tally
(N INT,
CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))
--===== Create and preset a loop counter
DECLARE @Counter INT
SET @Counter = 1
--===== Populate the table using the loop and couner
WHILE @Counter <= 11000
BEGIN
INSERT INTO dbo.Tally
(N)
VALUES (@Counter)
SET @Counter = @Counter + 1
END
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'Element01,Element02,Element03,Element04,Element05'
--===== Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)
--===== Add start and end commas to the Parameter so we can handle
-- single elements
SET @Parameter = ','+@Parameter +','
--===== Join the Tally table to the string at the character level and
-- when we find a comma, insert what's between that command and
-- the next comma into the Elements table
INSERT INTO @Elements
(Value)
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma
SELECT * FROM @Elements
I don't fully get how this part of the INSERT statement works:
SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
If N=1, then N+1=2 on each subsequent loop, I think the string being passed to this statement as @Parameter is getting truncated by the amount lobbed off in the prior pass.
Am I right to understand that with each loop the value of @Parameter passed get shorter by one delimiter and the string between the first set of delimiters? As in:
1rst time through: 'Element01,Element02,Element03,Element04,Element05'
2nd time through: ,Element02,Element03,Element04,Element05'
3rd time through: ,Element03,Element04,Element05'
and so on?
Is this what Jeff Moden means when he says "Again, all this does is find a comma and "remembers" its position. Then it uses CharIndex to find the next comma and inserts what's between the commas into a table variable. It quits looping when it runs out of commas. "
June 2, 2014 at 11:34 pm
Seriously? No one could answer that question?
June 3, 2014 at 12:06 am
KoldCoffee (6/2/2014)
Seriously? No one could answer that question?
Heh... Seriously? You couldn't answer that question? 😉 Keep it light, KC.
Look at the code again. You've taken the SUBSTRING out of context. What are the actual values of N according to the WHERE clause?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2014 at 8:20 am
OK I'll take a look.
(on 'seriously', it was said with a wounded tone).:crying:
June 3, 2014 at 12:27 pm
KoldCoffee (6/3/2014)
OK I'll take a look.(on 'seriously', it was said with a wounded tone).:crying:
Ah... got it.
If you look at the WHERE clause, "N" will only be those values that represent the character positions from left to right of where the delimiters occur.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2014 at 9:00 am
Luis Cazares (5/29/2014)
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
Oh man, I wish I saw something like this a year or so ago. I spent a heckuva long time trying to figure out where to put .value('.', 'nvarchar(MAX)') to make a variable stop printing weird &OXF or something when it hit char(10) and char(13). Thanks Luis.
An example of string splitting that has come up fairly often for me has been folder paths stored in tables that need to either be trimmed to the nth '\' or the value between the nth occurrences needs to be pulled out. It gets fun when people start mixing UNC '\\' paths with system 'C:\' paths.
I didn't see this code in the initial capitalization thread. It's what I've been using happily for a couple years. Not sure how it compares to the other solutions.
CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))
/***************************************************************************************************
Purpose:
Capitalize any lower case alpha character which follows any non alpha character or single quote.
Revision History:
Rev 00 - 24 Feb 2010 - George Mastros - Initial concept
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function
Rev 01 - 25 Sep 2010 - Jeff Moden
- Redaction for personal use and added documentation.
- Slight speed enhancement by adding additional COLLATE clauses that shouldn't have mattered
- and the reduction of multiple SET statements to just 2 SELECT statements.
- Add no-cap single-quote by single-quote to the filter.
***************************************************************************************************/
RETURNS VARCHAR(8000)
AS
BEGIN
----------------------------------------------------------------------------------------------------
DECLARE @Position INT
;
--===== Update the first character no matter what and then find the next postion that we
-- need to update. The collation here is essential to making this so simple.
-- A-z is equivalent to the slower A-Z
SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
--===== Do the same thing over and over until we run out of places to capitalize.
-- Note the reason for the speed here is that ONLY places that need capitalization
-- are even considered for @Position using the speed of PATINDEX.
WHILE @Position > 0
SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
----------------------------------------------------------------------------------------------------
RETURN @String;
END ;
June 4, 2014 at 9:28 am
sqldriver (6/4/2014)
I didn't see this code in the initial capitalization thread. It's what I've been using happily for a couple years. Not sure how it compares to the other solutions.
You can test it, but I guess it will be slower as this is a scalar function with a while loop in it.
The ProperCase Function thread has code to test the functions or you can test with your own information.
Try to modify the functions to be able to handle exactly what you need.
June 4, 2014 at 3:54 pm
Luis Cazares (6/4/2014)
sqldriver (6/4/2014)
I didn't see this code in the initial capitalization thread. It's what I've been using happily for a couple years. Not sure how it compares to the other solutions.You can test it, but I guess it will be slower as this is a scalar function with a while loop in it.
The ProperCase Function thread has code to test the functions or you can test with your own information.
Try to modify the functions to be able to handle exactly what you need.
Heh... I'm in a hell of a spot here... DelimitedSplit8K vs a very fast scalar function that I modified to be quicker. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2014 at 4:15 pm
Jeff Moden (6/4/2014)
Luis Cazares (6/4/2014)
sqldriver (6/4/2014)
I didn't see this code in the initial capitalization thread. It's what I've been using happily for a couple years. Not sure how it compares to the other solutions.You can test it, but I guess it will be slower as this is a scalar function with a while loop in it.
The ProperCase Function thread has code to test the functions or you can test with your own information.
Try to modify the functions to be able to handle exactly what you need.
Heh... I'm in a hell of a spot here... DelimitedSplit8K vs a very fast scalar function that I modified to be quicker. 😛
In the (almost) words of Paul Weller - "this is the Moden world."
June 4, 2014 at 5:28 pm
Jeff Moden (6/4/2014)
Luis Cazares (6/4/2014)
sqldriver (6/4/2014)
I didn't see this code in the initial capitalization thread. It's what I've been using happily for a couple years. Not sure how it compares to the other solutions.You can test it, but I guess it will be slower as this is a scalar function with a while loop in it.
The ProperCase Function thread has code to test the functions or you can test with your own information.
Try to modify the functions to be able to handle exactly what you need.
Heh... I'm in a hell of a spot here... DelimitedSplit8K vs a very fast scalar function that I modified to be quicker. 😛
I'm stunned. After some tests, it seems than in most cases the scalar function will perform better and it seems to scale a lot better. The cases where it won't perform better are cases where the duration on 1000 rows goes under 0.1 second so it shouldn't be a big deal.
Conclusion: Never say never.:-D
Viewing 15 posts - 46 through 59 (of 59 total)
You must be logged in to reply to this topic. Login to reply