Funny Test - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

The Yak Corral

Funny Test

Author

Topic

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-09-02 : 08:51:27

[code]
create table t (n varchar(800))
go
insert into t select 'mary had a little lamb...'
[/code]
Using only one select stmt & std. sql functions try to get rid of spare spaces between words in the string. I.e., it should be:

select <tra-lia-lia> from t

------------------------------
mary had a little lamb...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-02 : 08:59:48

the nasty way:


create table t (n varchar(800))

insert into t select 'mary    had a  little         lamb...'


Select replace(replace(replace(replace(n,'   ',' '),'   ',' '),'  ',' '),'  ',' ') From t

Drop table t

Corey

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-09-02 : 09:06:17

And what it does???

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 09:16:23

i think he meant:

Select replace(replace(replace(replace(n,' ',' '),' ',' '),' ',' '),' ',' ') From t

Go with the flow & have fun! Else fight the flow :)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-09-02 : 09:19:08

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21511

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-09-02 : 09:21:55

quote:
Originally posted by spirit1

i think he meant:

Select replace(replace(replace(replace(n,' ',' '),' ',' '),' ',' '),' ',' ') From t

Not only did he mean that, he said it!
It might have worked better inside a [code] section.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 09:24:04

ok i hate how that trims spaces :)))))))))

Go with the flow & have fun! Else fight the flow :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-02 : 10:57:09

Yeah... thought without tabbing the [code] section wouldn't matter

Corey

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-09-02 : 12:16:01

that's right!

but I gave only 1% that this would be something new for the sql-dream-team.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-03 : 11:25:07

declare @s varchar(128)
select @s = 'asdf asdf asdf dsf asdf dsf ds asdfasdf asdf asdf asd'
select replace(replace(replace(replace(@s,' ','[ ]'), '][',''), ' ',''), '[]', ' ')
asdf asdf asdf dsf asdf dsf ds asdfasdf asdf asdf asd

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-03 : 11:27:36

quote:
Originally posted by nr

declare @s varchar(128)
select @s = 'asdf asdf asdf dsf asdf dsf ds asdfasdf asdf asdf asd'
select replace(replace(replace(replace(@s,' ','[ ]'), '][',''), ' ',''), '[]', ' ')
asdf asdf asdf dsf asdf dsf ds asdfasdf asdf asdf asd

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-03 : 11:35:57

by the way...

I got it down to 3 replaces instead of 4:


select @s = 'asdf                         asdf     asdf  dsf  asdf  dsf  ds asdfasdf  asdf           asdf  asd'
Select replace(replace(replace(@s,'  ',' |'),'| ','|'),'|','')

Corey

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-09-05 : 18:32:15

quote:
Originally posted by Seventhnight

by the way...

I got it down to 3 replaces instead of 4:

select @s = 'asdf asdf asdf dsf asdf dsf ds asdfasdf asdf asdf asd'
Select replace(replace(replace(@s,' ',' |'),'| ','|'),'|','')

Corey

When posting I meant to see exactly this last answer.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-05 : 19:30:20

What do I win????

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-05 : 19:47:33

Sweet Corey!

You win the batton and have to post another one :)

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-05 : 19:59:00

alright... I'll be thinking