Get the First letter of each word within a string and returned a concatenated string
-
peterzeke (5/10/2011)
Million thanks, Jeff. I look forward to trying it out your sql solution.As I'm sure you've heard from many others before, your thoroughness of tackling a problem is really something to behold. I wish I had the time at work to be so complete when developing a solution and testing it. (A quick look at my email in-box shows I stopped reading SSC newsletters back in March! (still marked as "unread") -- except when the newsletter subject line read "Tally Oh!" -- of course, I knew to take moment to read that!) Finding time for R&D is tough these days...
Thanks again,
--Pete
Thank you for the very kind words, Pete. I'm glad to help. Thanks for the read on "Tally Oh!".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
peterzeke (5/10/2011)[hrI agree 100% -- I've pushed many times that we need to spend the resources to build a "pipeline" instead of maintaining the "bucket brigade."
I like that, I'm going to buy us all buckets...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
WayneS (5/10/2011)
FYI, here are the results on my system (with my code throw in to test also):--===== Test the RBAR function ========================================================================================Total Duration in Seconds: 12
--===== Test the NON RBAR function ========================================================================================
Total Duration in Seconds: 26
--===== Test the real set-based function ========================================================================================
Total Duration in Seconds: 6
--===== Test Wayne's real set-based solution ========================================================================================
Total Duration in Seconds: 9
Results speak for themselves :Whistling:
Thanks for the additional testing, Wayne (seems like I'm saying that more and more, lately). I love my 9 year old computer and just didn't take the time to transfer it to the "new" laptop. The differences in performance are staggering. Guess I figure if it runs good on "ol' Paint", it'll fly on newer boxes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You know Jeff, eventually "ol' Paint" needs to take a trip to the glue factory 😉
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
Jeff, what your function Abbreviate supposed to do ?
It seems to me that it does not do much:
select * from dbo.Abbreviate('aaa bbb ccccc')
Result:
a
select * from dbo.Abbreviate(' aaa .. bbb ,, ccccc ')
Result:
Time comparison with non-working function is not a good test case.
_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths -
Vedran Kesegic (5/10/2011)
Jeff, what your function Abbreviate supposed to do ?It seems to me that it does not do much:
select * from dbo.Abbreviate('aaa bbb ccccc')
Result:
a
It works just fine for what it was designed to do... recognize the first character and then capital letters or digits that follow a space.
SELECT * FROM dbo.Abbreviate('Bachelor of Science')SELECT * FROM dbo.Abbreviate('Bachelor Of Science')
Results:
Abbreviation---------------------
BS
(1 row(s) affected)
Abbreviation
---------------------
BOS
(1 row(s) affected)
Anyone who reads the code will recognize the use of the COLLATE clause. If they want it to recognize lower case words (atypical of the data examples given by Pete), they simply have to remove the COLLATE clause and make sure their default collation is case-insensitive or modify the "legal characters" it searches for.
Vedran Kesegic (5/10/2011)
select * from dbo.Abbreviate(' aaa .. bbb ,, ccccc ')Result:
Time comparison with non-working function is not a good test case.
Clean up your own act before you cast stones. 😉
SELECT dbo.str_FirstLetterOfWords_RBAR('# aaa .. bbb ,, ccccc ') AS AbbreviationSELECT dbo.str_FirstLetterOfWords_RBAR(' aaa .. bbb ,, ccccc ') AS Abbreviation
Result:
Abbreviation-------------------------------------------
#abc
(1 row(s) affected)
Abbreviation
-------------------------------------------
abc --<<<---<<< Notice the leading space!
(1 row(s) affected)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Pete,
Based on the findings in the post above, will you ever have a condition where the first character isn't a letter?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
T-SQL program fetching the first character of each word in the given string :
DECLARE @STRING VARCHAR(8000);
DECLARE @Pos int =1;
DECLARE @FINALSTRING VARCHAR(8000) = '';
DECLARE @COUNTER int = 0;
DECLARE @DELIMINATOR VARCHAR(1) = ' ';
SET @STRING = 'COMMUNITY GENERAL HOSPITAL';
/*Finds the last occurrence of the delimitated character*/
SET @COUNTER = LEN(@STRING) - CHARINDEX(@DELIMINATOR,REVERSE(@STRING))
/*Fetches first character*/
SET @FINALSTRING = SUBSTRING(@STRING,@Pos,1)
WHILE ( @Pos <= @COUNTER)
BEGIN
/*Finds the other occurrences of the delimitated character*/
SET @Pos = CHARINDEX(@DELIMINATOR,@STRING,@Pos+1)
SET @FINALSTRING = @FINALSTRING + substring(@STRING,@Pos+1,1);
END
SELECT @FINALSTRING
-
For completeness, here's a simple SQLCLR to perform the same task, appears to perform okay.
using System;using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Abbrev(SqlString sqlstr)
{
if (sqlstr.IsNull)
{
return SqlString.Null;
}
String str = sqlstr.Value;
StringBuilder sb = new StringBuilder(16 + (str.Length / 4));
bool bPrevSpace = true;
foreach (char ch in str)
{
if (Char.IsWhiteSpace(ch))
{
bPrevSpace = true;
}
else
{
if (bPrevSpace)
{
sb.Append(ch);
bPrevSpace = false;
}
}
}
return new SqlString(sb.ToString());
}
};
--===== Test the RBAR function ========================================================================================(1000000 row(s) affected)
Total Duration in Seconds: 30
--===== Test the NON RBAR function ========================================================================================
(1000000 row(s) affected)
Total Duration in Seconds: 58
--===== Test the real set-based function ========================================================================================
(1000000 row(s) affected)
Total Duration in Seconds: 9
--===== Test the SQLCLR function ========================================================================================
(1000000 row(s) affected)
Total Duration in Seconds: 5
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
select * from dbo.Abbreviate(' aaa .. bbb ,, ccccc ')--Result (without ;):
&#;x20;
Sory, result text was eaten by some html machinery - strange result was this second one example.
Clean up your own act before you cast stones. 😉
Today I can throw only cakes, drinks and ice-cream - my son was just born :hehe:
_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths -
Jeff Moden (5/10/2011)
Pete,Based on the findings in the post above, will you ever have a condition where the first character isn't a letter?
Jeff -- the first character should just be a letter.
By the way, by "first character" I'm referring to the first character of the entire string, not the first character of every word within the string.
(There is one exception where the healthcare provider's name is "1-800-contacts", but this provider will never play a role in my analyses, at least regarding a need to create abbreviations.)
Given the sketchy quality of healthcare data that we receive, however, it's possible something could slip by our scrubbing routines that we've never encountered before. If you've ever worked with healthcare data, I'm sure you know what I'm talking about.
-
Vedran Kesegic (5/11/2011)
Today I can throw only cakes, drinks and ice-cream - my son was just born :hehe:
Congrats! Happy early Father's Day!
Jimmy Piersal, on How to Diaper a Baby
Spread the diaper in the position of the diamond with you at bat. Then, fold second base down to home and set the baby on the pitcher's mound. Put first base and third together, bring up home plate and pin the three together. Of course, in case of rain, you gotta call the game and start all over again.
http://quotations.about.com/od/specialdays/a/funny_father.htm
-
Vedran Kesegic (5/11/2011)
select * from dbo.Abbreviate(' aaa .. bbb ,, ccccc ')--Result (without ;):
&#;x20;
Sory, result text was eaten by some html machinery - strange result was this second one example.
Clean up your own act before you cast stones. 😉
Today I can throw only cakes, drinks and ice-cream - my son was just born :hehe:
CONGRATULATIONS!!! :w00t:
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
Vedran Kesegic (5/11/2011)
Today I can throw only cakes, drinks and ice-cream - my son was just born :hehe:A very, very special occasion, indeed! Congratulations to you and your Wife, Vedran!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Vedran Kesegic (5/11/2011)
select * from dbo.Abbreviate(' aaa .. bbb ,, ccccc ')--Result (without ;):
&#;x20;
Sory, result text was eaten by some html machinery - strange result was this second one example.
Not a problem. It comes out correctly when you hit the "quote" button. Thats the "entitized" symbology for a space character. Thank you for the correction, though.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply