Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » SQL Server 2008 » T-SQL (SS2K8) » Multiple Space into one + 65,33,17,9,5,3,2
Add to briefcase
28 posts, Page 1 of 3
123»»»

Multiple Space into one + 65,33,17,9,5,3,2 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
karthik M
Posted Monday, September 24, 2012 6:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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)
BEGIN
RETURN   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,2

On 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
Sean Lange
Posted Monday, September 24, 2012 8:20 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy 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)
BEGIN
RETURN   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,2

On 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
Phil Parkin
Posted Monday, September 24, 2012 10:32 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal 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
Matt Miller (#4)
Posted Monday, September 24, 2012 10:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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
bitbucket-25253
Posted Monday, September 24, 2012 11:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 5,103, Visits: 20,220
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 poster

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read
Post #1363661
Jeff Moden
Posted Monday, September 24, 2012 2:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-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)
BEGIN
RETURN   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,2

On 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
karthik M
Posted Monday, September 24, 2012 8:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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 poster


oops...

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
bitbucket-25253
Posted Monday, September 24, 2012 9:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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 poster


oops...

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.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read
Post #1363769
karthik M
Posted Tuesday, September 25, 2012 12:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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
karthik M
Posted Tuesday, September 25, 2012 12:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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 performance


okay. why don't i use +2 or +3 instead of +1 from power of 2?
what is the logic behind this?


karthik
Post #1363803
« Prev Topic | Next Topic »

Add to briefcase
28 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.