Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Multiple Space into one + 65,33,17,9,5,3,2 Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, September 24, 2012 6:58 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, May 21, 2013 2:56 AM Points: 2,008, Visits: 2,469
 All,I have recently read Jeff Moden's Multiple space into one space article and i saw M's reply on this topic.He gave the below solution to resolve the same issue.`set @s = '*' + replicate(' ',7998) + '*'select len(@s)set @s = replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)), replicate(' ',32+1),' '),replicate(' ',16+1),' '),replicate(' ',8+1),' '),replicate(' ',4+1),' '),replicate(' ',2+1),' '),replicate(' ',1+1),' ')select len(@s)select @s``CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKII(@S VARCHAR(8000))RETURNS VARCHAR(8000)BEGINRETURN replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)), ' ',' '), ' ',' '), ' ',' '), ' ',' '), ' ',' '), ' ',' '), ' ',' ')END`Seems like both are same except one extra REPLACE.Spaces are manually added and in the first one REPLICATE function is used to do the same.My question is, what is this number denotes?65,33,17,9,5,3,2On what basis, the above numbers are selected. what is the mathematics behind these numbers?on what basis we have to choose the number?say for example , I have to delete space from the below string.select 'karthik keyan 44768 sql server 2008'which number i have to use? how many REPLACE i have to use? karthik
Post #1363464
 Posted Monday, September 24, 2012 8:20 AM
 SSCrazy Eights Group: General Forum Members Last Login: Yesterday @ 1:17 PM Points: 8,641, Visits: 8,273
 karthikeyan-444867 (9/24/2012)All,I have recently read Jeff Moden's Multiple space into one space article and i saw M's reply on this topic.He gave the below solution to resolve the same issue.`set @s = '*' + replicate(' ',7998) + '*'select len(@s)set @s = replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)), replicate(' ',32+1),' '),replicate(' ',16+1),' '),replicate(' ',8+1),' '),replicate(' ',4+1),' '),replicate(' ',2+1),' '),replicate(' ',1+1),' ')select len(@s)select @s``CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKII(@S VARCHAR(8000))RETURNS VARCHAR(8000)BEGINRETURN replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)), ' ',' '), ' ',' '), ' ',' '), ' ',' '), ' ',' '), ' ',' '), ' ',' ')END`Seems like both are same except one extra REPLACE.Spaces are manually added and in the first one REPLICATE function is used to do the same.My question is, what is this number denotes?65,33,17,9,5,3,2On what basis, the above numbers are selected. what is the mathematics behind these numbers?on what basis we have to choose the number?say for example , I have to delete space from the below string.select 'karthik keyan 44768 sql server 2008'which number i have to use? how many REPLACE i have to use?HUH??? _______________________________________________________________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 Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1363516
 Posted Monday, September 24, 2012 10:32 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 9:39 AM Points: 4,247, Visits: 9,500
 say for example , I have to delete space from the below string.select 'karthik keyan 44768 sql server 2008'which number i have to use? how many REPLACE i have to use?One. ____________________________________________________________________________________________Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:http://www.sqlservercentral.com/articles/Best+Practices/61537/If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1363616
 Posted Monday, September 24, 2012 10:46 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 5:22 PM Points: 6,998, Visits: 13,951
 Deleting space completely is an entirely different exercise. Like Phil mentioned - that just takes one REPLACE to do that.The numbers were chosen to avoid having to run the cleaning process multiple times: it makes sure that the replace doesn't leave a smaller set that still is more than 1 in a row without it having to do a ludicrous amount of operations within a given string. ----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1363627
 Posted Monday, September 24, 2012 11:55 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 12:39 PM Points: 5,103, Visits: 20,220
Post #1363661
 Posted Monday, September 24, 2012 2:11 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 4:35 PM Points: 32,930, Visits: 26,817
 karthikeyan-444867 (9/24/2012)All,I have recently read Jeff Moden's Multiple space into one space article and i saw M's reply on this topic.He gave the below solution to resolve the same issue.`set @s = '*' + replicate(' ',7998) + '*'select len(@s)set @s = replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)), replicate(' ',32+1),' '),replicate(' ',16+1),' '),replicate(' ',8+1),' '),replicate(' ',4+1),' '),replicate(' ',2+1),' '),replicate(' ',1+1),' ')select len(@s)select @s``CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKII(@S VARCHAR(8000))RETURNS VARCHAR(8000)BEGINRETURN replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)), ' ',' '), ' ',' '), ' ',' '), ' ',' '), ' ',' '), ' ',' '), ' ',' ')END`Seems like both are same except one extra REPLACE.Spaces are manually added and in the first one REPLICATE function is used to do the same.My question is, what is this number denotes?65,33,17,9,5,3,2On what basis, the above numbers are selected. what is the mathematics behind these numbers?on what basis we have to choose the number?say for example , I have to delete space from the below string.select 'karthik keyan 44768 sql server 2008'which number i have to use? how many REPLACE i have to use?Most of the numbers come from powers of 2 + 1. In this particular case, it's an optimization for performance. --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." For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1363715
 Posted Monday, September 24, 2012 8:55 PM
 SSCrazy Group: General Forum Members Last Login: Tuesday, May 21, 2013 2:56 AM Points: 2,008, Visits: 2,469
 bitbucket-25253 (9/24/2012)Your request concerned removing a single space, while Jeff's arcticle's objective was:This article explains the "puzzle solving" behind a common set based method to replace unknown numbers of adjacent spaces with a single space Bold emphasis added by this posteroops...my string is select 'karthik keyan 44768 sql server 2008 'I wrongly used only one space between all the string. Now, How many REPLACE I have to use? On what basis the number of REPLACE selected? karthik
Post #1363760
 Posted Monday, September 24, 2012 9:59 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 12:39 PM Points: 5,103, Visits: 20,220
 karthikeyan-444867 (9/24/2012)bitbucket-25253 (9/24/2012)Your request concerned removing a single space, while Jeff's arcticle's objective was:This article explains the "puzzle solving" behind a common set based method to replace unknown numbers of adjacent spaces with a single space Bold emphasis added by this posteroops...my string is select 'karthik keyan 44768 sql server 2008 'I wrongly used only one space between all the string. Now, How many REPLACE I have to use? On what basis the number of REPLACE selected?In order to more readily view the code I have replaced blanks (spaces) with asteriks` DECLARE @K VARCHAR(46) SET @K = 'karthik****keyan***44768**sql*server*2008**' --As modified SELECT @K,REPLACE(@K,'*','') /* Result: karthik****keyan***44768**sql*server*2008** karthikkeyan44768sqlserver2008 */`Hopefully to make the REPLACE function more understandable I did the following` DECLARE @K VARCHAR(46) SET @K = 'karthik****keyan***44768**sql*server*2008**' --As modified SELECT @K,REPLACE(@K,'**','') /* Results: karthik****keyan***44768**sql*server*2008** karthikkeyan*44768sql*server*2008 */ ` If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read
Post #1363769
 Posted Tuesday, September 25, 2012 12:20 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, May 21, 2013 2:56 AM Points: 2,008, Visits: 2,469
 i think...Again my requiremtn is wrong..I just corrected it now...`select ''karthik keyan 44768 sql server 2008 '` Expected Output:`select ''karthik keyan 44768 sql server 2008'` i.e single space between each and every word.If I use the below code, it will replace just all the spaces. right?`DECLARE @K VARCHAR(46) SET @K = 'karthik****keyan***44768**sql*server*2008**' --As modified SELECT @K,REPLACE(@K,'**','') /* Results: karthik****keyan***44768**sql*server*2008** karthikkeyan*44768sql*server*2008 */ `Now..How many REPLACE i have to use? On what basis the number of REPLACE will be choosed? karthik
Post #1363801
 Posted Tuesday, September 25, 2012 12:22 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, May 21, 2013 2:56 AM Points: 2,008, Visits: 2,469
 Most of the numbers come from powers of 2 + 1. In this particular case, it's an optimization for performanceokay. why don't i use +2 or +3 instead of +1 from power of 2?what is the logic behind this? karthik
Post #1363803

 Permissions