Replace repeated Char in a string by the Char itself

Question

Replace repeated Char in a string by the Char itself

Viewing 2 posts - 16 through 16 (of 16 total)

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

Jeff Moden SSC Guru Points: 1004546 More actions Follow · Answer 1

Henk Schreij (5/22/2011)
Jeff, you're right, thank you for your explanation.
Sorry for my bold comment. :unsure:

I knew I should have added a smiley face or something. I took no exception at all to what you were saying. I was just explaining why I normally use a Temp Table for these types of things.

For anyone that's interested, here's one way I might build test data for this problem to determine which solutions a) returned correct answers and b) did it with the best performance.

--===== Conditionally drop the test table to make reruns in SSMS easier.

-- This is NOT a part of the actual solution

IF OBJECT_ID('tempdb..#JBMTest','U') IS NOT NULL DROP TABLE #JBMTestl

;

--===== Create the test table for this experiment. Details are in the code comments below.

-- Again, this is NOT a part of the actual solution. We're just building test data here.

SELECT t.N,

String =

( --== Build a row of data consisting of a random number of instances where some

-- random number of hash marks are followed by a single random letter of the

-- alphabet. There will be 1 to 20 hash marks for each "segment" and 1 to 100

-- segements per row

SELECT REPLICATE('#',ABS(CHECKSUM(NEWID()))%20+1) + CHAR(ABS(CHECKSUM(NEWID()))%25+65)

FROM dbo.Tally t1

WHERE t1.N BETWEEN 1 AND ABS(CHECKSUM(NEWID()))%100+1

AND t1.N <> -t.N --External reference necessary to make different rows

FOR XML PATH('')

) --== Terminate the row with 0 to 4 more hash marks

+ (SELECT REPLICATE('#',ABS(CHECKSUM(NEWID()))%5))

FROM dbo.Tally t

WHERE t.N BETWEEN 1 AND 10000

;

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

Jeff Moden SSC Guru Points: 1004546 More actions Follow · Answer 2

Jeff Moden (5/22/2011)
Henk Schreij (5/22/2011)
Jeff, you're right, thank you for your explanation.
Sorry for my bold comment. :unsure:
I knew I should have added a smiley face or something. I took no exception at all to what you were saying. I was just explaining why I normally use a Temp Table for these types of things.
For anyone that's interested, here's one way I might build test data for this problem to determine which solutions a) returned correct answers and b) did it with the best performance.
--===== Conditionally drop the test table to make reruns in SSMS easier.
-- This is NOT a part of the actual solution
IF OBJECT_ID('tempdb..#JBMTest','U') IS NOT NULL DROP TABLE #JBMTestl
;
--===== Create the test table for this experiment. Details are in the code comments below.
-- Again, this is NOT a part of the actual solution. We're just building test data here.
SELECT t.N,
String =
( --== Build a row of data consisting of a random number of instances where some
-- random number of hash marks are followed by a single random letter of the
-- alphabet. There will be 1 to 20 hash marks for each "segment" and 1 to 100
-- segements per row
SELECT REPLICATE('#',ABS(CHECKSUM(NEWID()))%20+1) + CHAR(ABS(CHECKSUM(NEWID()))%25+65)
FROM dbo.Tally t1
WHERE t1.N BETWEEN 1 AND ABS(CHECKSUM(NEWID()))%100+1
AND t1.N <> -t.N --External reference necessary to make different rows
FOR XML PATH('')
) --== Terminate the row with 0 to 4 more hash marks
+ (SELECT REPLICATE('#',ABS(CHECKSUM(NEWID()))%5))
FROM dbo.Tally t
WHERE t.N BETWEEN 1 AND 10000
;

Of course, after that preliminary testing, I'd then turn the table into a million row table just to be sure. With only very few exceptions, my take on it all is that if it doesn't work well and quickly on a million rows, then it's not ready for production. It helps keep my users from testing it for me when they can least afford to do so.

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

Replace repeated Char in a string by the Char itself

Cookies on SQLServerCentral