Repeating characters - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Repeating characters

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-11-19 : 11:29:48

I would like to repeat characters in a string, but not repeat the entire string. E.g. a string like this 'johndoe' I would like as 'jjjooohhhnnndddoooeee', and using the REPLICATE function would do 'johndoejohndoejohndoe'. Also, I would like this inline in a SELECT statement. I am using SQL 7 so I have no use for UDFs.

Thanks!

Sarah Berger MCSD

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-11-19 : 11:37:03

Try this

DECLARE @OriginalString VARCHAR(100), @NewString VARCHAR(300), @Character CHAR(1), @Repeat INT, @Position INT

SET @OriginalString = 'johndoe'
SET @NewString = ''
SET @Repeat = 3
SET @Position = 1

WHILE @Position <= DATALENGTH(@OriginalString)
BEGIN
SET @Character = SUBSTRING(@OriginalString, @Position, 1)
SET @NewString = @NewString + REPLICATE(@Character, @Repeat)
SET @Position = @Position + 1
END

SELECT @NewString

Raymond

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-19 : 22:09:03

[code]
create table #People(id int identity(0,1), Person varchar(20) primary key)

insert into #People
select 'Ed' union
select 'Pete' union
select 'Mitch' union
select 'Lewy' union
select 'Paul' union
select 'Mary' union
select 'Lisa' union
select 'Tom' union
select 'Jeff'

SELECT ISNULL(substring(stuff(person,1,0,replicate(substring(person,1,1),3)),1,3),'') +
ISNULL(substring(stuff(person,2,0,replicate(substring(person,2,1),3)),2,3),'') +
ISNULL(substring(stuff(person,3,0,replicate(substring(person,3,1),3)),3,3),'') +
ISNULL(substring(stuff(person,4,0,replicate(substring(person,4,1),3)),4,3),'') +
ISNULL(substring(stuff(person,5,0,replicate(substring(person,5,1),3)),5,3),'')
FROM #People

DROP TABLE #People
[/code]

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-11-20 : 04:14:01

Nice answer ehorn, but limited to 5 chars not the maximum datalength of anyname. Could it be made more generic?

Raymond

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-20 : 04:39:15

EUREKA...CROSS JOIN ROCKS!!!

Note: A tally table is always a useful thing to have around...


	create table #numbers
	(
		number int
	)
	
	go
	
	insert into #numbers
	select 1
	union
	select 2
	union
	select 3
	union
	select 4
	union
	select 5
	union
	select 6
	union
	select 7
	union
	select 8
	union
	select 9
	go
	
	
	declare @string varchar(10)
	set @string = 'johndoe'
	declare @replicatedString varchar(100)
	
	select @replicatedstring = coalesce(@replicatedString + '', '') + eachchar from
	(
		select substring(@string, number, 1) as eachchar from #numbers where number <= len(@string)
	) a
	cross join #numbers b
	where b.number < 4
	
	select @replicatedstring

	drop table #numbers
	go

Of course, the real challenge is to make it work inline with the select...(sigh) what a shame!

Owais

Where there's a will, I want to be in it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources