Multiple Space into one + 65,33,17,9,5,3,2

Question

Multiple Space into one + 65,33,17,9,5,3,2

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

Thanks Matt & Jeff.

I have also done some quick simulations to see maximum lengths of strings that can be handled with each value of N. These are the results so far:
N=1 10
N=2 38
N=3 286
N=4 4622
N=5 151534

Can you help me to understand this one too?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Jeff's code is working fine in SQLSERVER & SYBASE (with some small change) . Yes, Instead of '', we have to use Null. As sybase never replace any string with nothing, we have to use Null.

I can understand easily Jeff's code.

I can't understand M's technique. Especially determining the length.

I have also done some quick simulations to see maximum lengths of strings that can be handled with each value of N. These are the results so far:
N=1 10
N=2 38
N=3 286
N=4 4622
N=5 151534

karthik

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

karthikeyan-444867 (10/1/2012)
Jeff's code is working fine in SQLSERVER & SYBASE (with some small change) . Yes, Instead of '', we have to use Null. As sybase never replace any string with nothing, we have to use Null.

Have you tried using a space instead of an empty string?

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 4

yes. I can see double space.

say for example,

I used k arthi

k þarthi

i.e two space.

my statement:

str_replace(str_replace(str_replace('k arthi'),' ',' '+char(7)),char(7)+' ',' '),char(7),' ')

str_replace(str_replace(str_replace('k arthi'),' ',' '+char(7)),char(7)+' ',null),char(7),null)

is working fine.

karthik

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

karthikeyan-444867 (10/1/2012)
yes. I can see double space.
say for example,
I used k arthi
k þarthi
i.e two space.
my statement:
str_replace(str_replace(str_replace('k arthi'),' ',' '+char(7)),char(7)+' ',' '),char(7),' ')
str_replace(str_replace(str_replace('k arthi'),' ',' '+char(7)),char(7)+' ',null),char(7),null)
is working fine.

You obviously didn't take my recommendation that that's no longer the way I recommend replacing multiple spaces.

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 6

karthik M

One Orange Chip

Points: 29139

October 1, 2012 at 9:06 pm

#1544213

?

how? is it because of using NULL?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 7

I have used your code in SQL without changin anything. But in Sybase, I have done some customization.

karthik

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

karthik M (10/1/2012)
I have used your code in SQL without changin anything. But in Sybase, I have done some customization.

That's my whole point... WHICH code? The CHAR(7) replacement code is an order of magnitude slower than the nested space replacers and no NULL is involved.

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 9

DECLARE @OriginalString VARCHAR(70)

--SET @OriginalString = 'karthik keyan 44768 sql server 2008 '

SET @OriginalString = 'karthik keyan 44768 sql server 2008 '

SELECT REPLACE(

REPLACE(

LTRIM(RTRIM(@OriginalString))

,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model

,CHAR(7)+' ','') --Changes the XO model to nothing

,CHAR(7),'') AS CleanString --Changes the remaining X's to nothing

WHERE CHARINDEX(' ',@OriginalString) > 0

/* Result[/code

I used the above technique only. Do you mean multpile replace with 2^n+1

method?

Without understanding that technique, I feel like just copy & paste the code.

I can understand your code. so if my lead asked anything about that technique, I can explain. But the other one, I really don't understand the concepts completely.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 10

Jeff,

Can you also explain the another method in detail ?

I am really curious to know how the replace with some number combination is working?

65,33,17,9,5,3,2

karthik

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

karthik M (10/3/2012)
Jeff,
Can you also explain the another method in detail ?
I am really curious to know how the replace with some number combination is working?
65,33,17,9,5,3,2

That's already been explained, Karthik on this thread and the article thread. What about it don't you understand?

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 12

I don't understand the below points.

1) Number of REPLACE selected (on what basis?)

say for example,

'k a r th i'

how many replace i have to use ? ( not your method, your's is constant 3 replace)

'k a r th i'

how many replace i have to use ? ( not your method, your's is constant 3 replace)

2)

I have also done some quick simulations to see maximum lengths of strings that can be handled with each value of N. These are the results so far:
N=1 10
N=2 38
N=3 286
N=4 4622
N=5 151534

how these values are derived?

karthik

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 13

karthik M (10/5/2012)
I don't understand the below points.
1) Number of REPLACE selected (on what basis?)
say for example,
'k a r th i'
how many replace i have to use ? ( not your method, your's is constant 3 replace)
'k a r th i'
how many replace i have to use ? ( not your method, your's is constant 3 replace)
2)
I have also done some quick simulations to see maximum lengths of strings that can be handled with each value of N. These are the results so far:
N=1 10
N=2 38
N=3 286
N=4 4622
N=5 151534
how these values are derived?

The max size of a VARCHAR in SQL Server (NOT including VARCHAR(MAX)) is 8000. If we round that UP to the nearest power of 2, we end up with the number "8192" and that's what we'll use as the "target" for our calculations as being the longest number of spaces that we'd need to reduce to 1.

We'll also NOT consider the +1 optimization just to keep things simple with round numbers.

If we assume that the largest number we need to change a number of spaces to 1 is 32, we end up with the following sequence of powers of 2. You can easily duplicate this on a spreadsheet.

Number of SpacesDivide bySpaces Left

8192 32 256

256 16 16

16 8 2

2 4 1

1 2 1

Notice that we have only 5 divisions necessary to complete the sequence of powers of 2 down to 2^1.

Since we have two occurances of "1" space left, you would think that starting the sequence at 32 is overkill and would try 16 as the first number in the sequence. Here's what happens when you try that...

Number of SpacesDivide bySpaces Left

8192 16 512

512 8 64

64 4 16

16 2 8

8 2 4

4 2 2

2 2 1

Instead of 5 divisions, we now have 7 and we've had to repeat some of the divisions. We could optimize my changing the sequence but there's no guarantee that a custom sequence would work for all values.

Thinking that maybe we should go with the next larger number of an even power of 2, let's see what happens if we start the sequence at 64.

Number of SpacesDivide bySpaces Left

8192 64 128

128 32 4

4 16 1

1 8 1

1 4 1

1 2 1

As you can see, that's 6 divisions which is still more than the original 5 that we ended up with for the original starting value of 32. For reducing 8000 spaces to 1, 2^5 or 32 is the optimal starting value for the sequence that is based on changing a certain number of spaces to 1.

Because a string will usually have interceeding non-blank characters in it, adding an extra divide by 2 will make up for any possible straglers.

As previously stated, I've (shamefully) not actually tested for a performance difference between the powers of 2 division and the +1 optimization.

Because the nested replaces keep making the string shorter and shorter, the 5 replaces take less time than the 3 replaces that I used in the original method. You can easily prove that to yourself by writting some test code.

If that doesn't answer your questions, then I'm out of tricks to show you and you're going to have to get busy with Google and a spreadsheet. Here's the link to a message that Joe Celko referred to in the discussion to get you started. The rest is up to you.

http://www.orafaq.com/usenet/comp.databases.theory/2004/02/04/0030.htm

--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)