Board index MS SQL Server

little SQL puzzle

little SQL puzzle

Post by Bob » Sat, 13 Sep 2003 17:23:08



What is the best way to replace all occurrences of more than one character
together with just one character?

I'm interested in collapsing whitespace within strings, but I'll use 'X' to be
clear in this example:

'aaaaXaaaXXXaaaXXaa' would collapse to 'aaaaXaaaXaaaXaa'

I know it can be done with iterative calls to REPLACE, but I'm hoping to find a
non-procedural solution.

TIA,
Bob

 
 
 
Top

little SQL puzzle

Post by Gert-Jan Stri » Sat, 13 Sep 2003 19:11:57


Bob,

the following statement will do what you want for a varchar column of
over 4000 characters, and will do it in one run.

update MyTable
set  MyVarcharColumn =
       Replace(
       Replace(
       Replace(
       Replace(
       Replace(MyVarCharColumn, 'XXXXXXXXXXX','X')
                              , 'XXXXXXX','X')
                              , 'XXXXX','X')
                              , 'XXX','X')
                              , 'XX','X')

Gert-Jan


> What is the best way to replace all occurrences of more than one character
> together with just one character?

> I'm interested in collapsing whitespace within strings, but I'll use 'X' to be
> clear in this example:

> 'aaaaXaaaXXXaaaXXaa' would collapse to 'aaaaXaaaXaaaXaa'

> I know it can be done with iterative calls to REPLACE, but I'm hoping to find a
> non-procedural solution.

> TIA,
> Bob


 
 
 
Top

little SQL puzzle

Post by Bob » Sat, 13 Sep 2003 19:38:56


Brilliant. Thank you.

Bob


> Bob,

> the following statement will do what you want for a varchar column of
> over 4000 characters, and will do it in one run.

> update MyTable
> set  MyVarcharColumn =
>        Replace(
>        Replace(
>        Replace(
>        Replace(
>        Replace(MyVarCharColumn, 'XXXXXXXXXXX','X')
>                               , 'XXXXXXX','X')
>                               , 'XXXXX','X')
>                               , 'XXX','X')
>                               , 'XX','X')

> Gert-Jan


> > What is the best way to replace all occurrences of more than one character
> > together with just one character?

> > I'm interested in collapsing whitespace within strings, but I'll use 'X' to
be
> > clear in this example:

> > 'aaaaXaaaXXXaaaXXaa' would collapse to 'aaaaXaaaXaaaXaa'

> > I know it can be done with iterative calls to REPLACE, but I'm hoping to
find a
> > non-procedural solution.

> > TIA,
> > Bob

 
 
 
Top

little SQL puzzle

Post by Bob » Sat, 13 Sep 2003 22:21:24


Alas! It does not work for more than 4000 characters as you say. The limit is
196 characters.

The general solution is this:
x{n} - 1 = maximum characters allowed with n REPLACE statements
x{0} = 2 and Phi{n} is the prime number function starting with Phi{1} = 2
x{n + 1} = (x{n} - (Phi{n} - 1)) * Phi{n} + (Phi{n} -1)

doing out the math:
x{1} = (2 - (2 - 1)) * 2 + (2 - 1) = 3
x{2} = (3 - (3 - 1)) * 3 + (3 - 1) = 5
x{3} = (5 - (5 - 1)) * 5 + (5 - 1) = 9
x{4} = (9 - (7 - 1)) * 7 + (7 - 1) = 27
x{5} = (27 - (11 - 1)) * 11 + (11 - 1) = 197  -- this is the 5 replace limit + 1
... but from here it increases more rapidly...
x{6} = (197- (13 - 1)) * 13 + (13 - 1) = 2417
x{7} = (2417- (17 - 1)) * 17 + (17 - 1) = 40833

So a minimum of seven REPLACEs are needed in a single statement to always cover
the varchar data type in MS SQL.

Bob


> Bob,

> the following statement will do what you want for a varchar column of
> over 4000 characters, and will do it in one run.

> update MyTable
> set  MyVarcharColumn =
>        Replace(
>        Replace(
>        Replace(
>        Replace(
>        Replace(MyVarCharColumn, 'XXXXXXXXXXX','X')
>                               , 'XXXXXXX','X')
>                               , 'XXXXX','X')
>                               , 'XXX','X')
>                               , 'XX','X')

> Gert-Jan


> > What is the best way to replace all occurrences of more than one character
> > together with just one character?

> > I'm interested in collapsing whitespace within strings, but I'll use 'X' to
be
> > clear in this example:

> > 'aaaaXaaaXXXaaaXXaa' would collapse to 'aaaaXaaaXaaaXaa'

> > I know it can be done with iterative calls to REPLACE, but I'm hoping to
find a
> > non-procedural solution.

> > TIA,
> > Bob

 
 
 
Top

little SQL puzzle

Post by Gert-Jan Stri » Sat, 13 Sep 2003 23:42:26


My appologies. I posted the wrong archive stuff. The correct query is
posted below. As you can see, 6 replacements is enough.

set nocount on
create table #t(id int,col1 varchar(4000))



begin


end

update #t
set  col1 =
       Replace(
       Replace(
       Replace(
       Replace(
       Replace(
       Replace(col1, replicate('X',132),'X')
                   , replicate('X', 21),'X')
                   , replicate('X',  6),'X')
                   , replicate('X',  3),'X')
                   , replicate('X',  2),'X')
                   , replicate('X',  2),'X')

select * from #t where col1<>'aXa'
drop table #t

Gert-Jan


> Alas! It does not work for more than 4000 characters as you say. The limit is
> 196 characters.

> The general solution is this:
> x{n} - 1 = maximum characters allowed with n REPLACE statements
> x{0} = 2 and Phi{n} is the prime number function starting with Phi{1} = 2
> x{n + 1} = (x{n} - (Phi{n} - 1)) * Phi{n} + (Phi{n} -1)

> doing out the math:
> x{1} = (2 - (2 - 1)) * 2 + (2 - 1) = 3
> x{2} = (3 - (3 - 1)) * 3 + (3 - 1) = 5
> x{3} = (5 - (5 - 1)) * 5 + (5 - 1) = 9
> x{4} = (9 - (7 - 1)) * 7 + (7 - 1) = 27
> x{5} = (27 - (11 - 1)) * 11 + (11 - 1) = 197  -- this is the 5 replace limit + 1
> ... but from here it increases more rapidly...
> x{6} = (197- (13 - 1)) * 13 + (13 - 1) = 2417
> x{7} = (2417- (17 - 1)) * 17 + (17 - 1) = 40833

> So a minimum of seven REPLACEs are needed in a single statement to always cover
> the varchar data type in MS SQL.

> Bob



> > Bob,

> > the following statement will do what you want for a varchar column of
> > over 4000 characters, and will do it in one run.

> > update MyTable
> > set  MyVarcharColumn =
> >        Replace(
> >        Replace(
> >        Replace(
> >        Replace(
> >        Replace(MyVarCharColumn, 'XXXXXXXXXXX','X')
> >                               , 'XXXXXXX','X')
> >                               , 'XXXXX','X')
> >                               , 'XXX','X')
> >                               , 'XX','X')

> > Gert-Jan


> > > What is the best way to replace all occurrences of more than one character
> > > together with just one character?

> > > I'm interested in collapsing whitespace within strings, but I'll use 'X' to
> be
> > > clear in this example:

> > > 'aaaaXaaaXXXaaaXXaa' would collapse to 'aaaaXaaaXaaaXaa'

> > > I know it can be done with iterative calls to REPLACE, but I'm hoping to
> find a
> > > non-procedural solution.

> > > TIA,
> > > Bob

 
 
 
Top

little SQL puzzle

Post by Steve Kas » Sun, 14 Sep 2003 03:08:01


Gert-Jan and Bob,

  A little over a year ago, Robert Carnegie (no relation?) posted a
brilliant
solution that uses only 3 replaces regardless of the longest repetition.
Here's a slight modification, which uses 4 replaces, but handles
strings up to the full 8000 character limit (his only worked
up to 4000):

update MyTable
set  MyVarcharColumn =
       Replace(
       Replace(
       Replace(
       Replace(MyVarcharColumn, 'XX' ,'<>')
                              , '><' ,''  )
                              , '<>' ,'X' )
                              , 'XX' ,'X' )

The earlier thread is here:  http://tinyurl.com/n735

Nonetheless, nice job with the Phi function, Bob!

-- Steve Kass
-- Drew University
-- Ref: 8A2779DA-A9A3-44D9-960E-5136250D6BFA


>Alas! It does not work for more than 4000 characters as you say. The limit is
>196 characters.

>The general solution is this:
>x{n} - 1 = maximum characters allowed with n REPLACE statements
>x{0} = 2 and Phi{n} is the prime number function starting with Phi{1} = 2
>x{n + 1} = (x{n} - (Phi{n} - 1)) * Phi{n} + (Phi{n} -1)

>doing out the math:
>x{1} = (2 - (2 - 1)) * 2 + (2 - 1) = 3
>x{2} = (3 - (3 - 1)) * 3 + (3 - 1) = 5
>x{3} = (5 - (5 - 1)) * 5 + (5 - 1) = 9
>x{4} = (9 - (7 - 1)) * 7 + (7 - 1) = 27
>x{5} = (27 - (11 - 1)) * 11 + (11 - 1) = 197  -- this is the 5 replace limit + 1
>... but from here it increases more rapidly...
>x{6} = (197- (13 - 1)) * 13 + (13 - 1) = 2417
>x{7} = (2417- (17 - 1)) * 17 + (17 - 1) = 40833

>So a minimum of seven REPLACEs are needed in a single statement to always cover
>the varchar data type in MS SQL.

>Bob



>>Bob,

>>the following statement will do what you want for a varchar column of
>>over 4000 characters, and will do it in one run.

>>update MyTable
>>set  MyVarcharColumn =
>>       Replace(
>>       Replace(
>>       Replace(
>>       Replace(
>>       Replace(MyVarCharColumn, 'XXXXXXXXXXX','X')
>>                              , 'XXXXXXX','X')
>>                              , 'XXXXX','X')
>>                              , 'XXX','X')
>>                              , 'XX','X')

>>Gert-Jan


>>>What is the best way to replace all occurrences of more than one character
>>>together with just one character?

>>>I'm interested in collapsing whitespace within strings, but I'll use 'X' to

>be

>>>clear in this example:

>>>'aaaaXaaaXXXaaaXXaa' would collapse to 'aaaaXaaaXaaaXaa'

>>>I know it can be done with iterative calls to REPLACE, but I'm hoping to

>find a

>>>non-procedural solution.

>>>TIA,
>>>Bob

 
 
 
Top

little SQL puzzle

Post by Steve Kas » Sun, 14 Sep 2003 03:11:59


Oops.  I forgot to mention that the solution I posted requires
there to exist two char values that are guaranteed never to be
in the strings you are replacing.

SK

 
 
 
Top

little SQL puzzle

Post by Gert-Jan Stri » Sun, 14 Sep 2003 10:55:59


Excellent solution. I assume Robert's solution did not work beyond 4000
characters because of overflow issues when replacing one character with
two, something you worked around here.

Just for completeness, the 6 replaces I posted will also work on the
entire 8000 characters (proving the Phi function does not apply or is
incorrect)

Gert-Jan


> Gert-Jan and Bob,

>   A little over a year ago, Robert Carnegie (no relation?) posted a
> brilliant
> solution that uses only 3 replaces regardless of the longest repetition.
> Here's a slight modification, which uses 4 replaces, but handles
> strings up to the full 8000 character limit (his only worked
> up to 4000):

> update MyTable
> set  MyVarcharColumn =
>        Replace(
>        Replace(
>        Replace(
>        Replace(MyVarcharColumn, 'XX' ,'<>')
>                               , '><' ,''  )
>                               , '<>' ,'X' )
>                               , 'XX' ,'X' )

> The earlier thread is here:  http://tinyurl.com/n735

> Nonetheless, nice job with the Phi function, Bob!

> -- Steve Kass
> -- Drew University
> -- Ref: 8A2779DA-A9A3-44D9-960E-5136250D6BFA


> >Alas! It does not work for more than 4000 characters as you say. The limit is
> >196 characters.

> >The general solution is this:
> >x{n} - 1 = maximum characters allowed with n REPLACE statements
> >x{0} = 2 and Phi{n} is the prime number function starting with Phi{1} = 2
> >x{n + 1} = (x{n} - (Phi{n} - 1)) * Phi{n} + (Phi{n} -1)

> >doing out the math:
> >x{1} = (2 - (2 - 1)) * 2 + (2 - 1) = 3
> >x{2} = (3 - (3 - 1)) * 3 + (3 - 1) = 5
> >x{3} = (5 - (5 - 1)) * 5 + (5 - 1) = 9
> >x{4} = (9 - (7 - 1)) * 7 + (7 - 1) = 27
> >x{5} = (27 - (11 - 1)) * 11 + (11 - 1) = 197  -- this is the 5 replace limit + 1
> >... but from here it increases more rapidly...
> >x{6} = (197- (13 - 1)) * 13 + (13 - 1) = 2417
> >x{7} = (2417- (17 - 1)) * 17 + (17 - 1) = 40833

> >So a minimum of seven REPLACEs are needed in a single statement to always cover
> >the varchar data type in MS SQL.

> >Bob



> >>Bob,

> >>the following statement will do what you want for a varchar column of
> >>over 4000 characters, and will do it in one run.

> >>update MyTable
> >>set  MyVarcharColumn =
> >>       Replace(
> >>       Replace(
> >>       Replace(
> >>       Replace(
> >>       Replace(MyVarCharColumn, 'XXXXXXXXXXX','X')
> >>                              , 'XXXXXXX','X')
> >>                              , 'XXXXX','X')
> >>                              , 'XXX','X')
> >>                              , 'XX','X')

> >>Gert-Jan


> >>>What is the best way to replace all occurrences of more than one character
> >>>together with just one character?

> >>>I'm interested in collapsing whitespace within strings, but I'll use 'X' to

> >be

> >>>clear in this example:

> >>>'aaaaXaaaXXXaaaXXaa' would collapse to 'aaaaXaaaXaaaXaa'

> >>>I know it can be done with iterative calls to REPLACE, but I'm hoping to

> >find a

> >>>non-procedural solution.

> >>>TIA,
> >>>Bob

 
 
 
Top

1. A little aggreagate puzzle

Hello!

I cannot figure out a simple way to do this, even if know that should
be possible.

I have a tabel that conists of ID's that are not unique, they will be
grouped by, and there is two other columns, dFrom and dTo, that is month
from and month to.


1. Count everyone that ended in the period that also started in the period
2. Count everyone that ended in the period that started before the period
3. count everyone new in the period


----------------------------------
id    ended1    ended2    new
1    1              1             4
---------------------------------

Here is all the code needed:
---------------------------------------------------
drop table #temp
go
create table #temp (
  id int,
  dFrom char(2),
  dTo char(2))

insert into #temp(id,dFrom,dTo) values ( 1,'01','')
insert into #temp(id,dFrom,dTo) values ( 2,'02','04')
insert into #temp(id,dFrom,dTo) values ( 2,'01','02')
insert into #temp(id,dFrom,dTo) values ( 3,'02','')
insert into #temp(id,dFrom,dTo) values ( 3,'02','03')
insert into #temp(id,dFrom,dTo) values ( 4,'03','04')





select *
from #temp
------------------------------------------------

Hope for help!!

Marius

2. Verzoek om afstudeer opdracht

3. A Little Puzzle for Math heads

4. WinXP error writing to Program Files

5. A little puzzle

6. Accpac DOS DDF

7. Annoying little puzzle

8. Inserting a generated FK value - Newbie

9. A little puzzle

10. Date puzzle has me puzzled

11. Solve this Paradox puzzle...! (a real puzzle)

12. ?SQL and #Date Formats# puzzle

13. SQL Puzzle (Long)


All times are UTC
Board index