Replace single or multiple hyphen(-) with a space.

Question

Post reply

Replace single or multiple hyphen(-) with a space.

Ashwin M N

Hall of Fame

Points: 3668
More actions
June 29, 2010 at 9:46 am

#226267

I have a column with multiple hyphens(-).I want to replace the multiple or single hyphens with a space.
CREATE TABLE TEMP (ID INT IDENTITY(1,1),Discount varchar(100), Discounturl varchar(100))
INSERT INTO TEMP(Discount)
SELECT'Save 10% On---Apparels At disc.com'
UNION
SELECT'All Sony Batteries 20% Off With Code--"lith"'
UNION
SELECT'50% Off ALL Brazil Charms Coupon Code:--1345'
UNION
SELECT'At Least 10%------Off All shoes At lutur.com'
UNION
SELECT'Micro Software---At Up-To 75%--Off For all!'
UNION
SELECT'Hundreds Of Items Up To 75% Off bright.com - Plus Sizes 14-28.'
UNION
SELECT'At Least 70%--Off At sigma.com'
UNION
SELECT'At Least-60% Off--At letyour.com'
UNION
SELECT'At Least-50% Off At leftover.com'
select * from temp
I want the single or multiple hyphens replaced by a single space and update to Discounturl column.i.e
UPDATE Temp set Discounturl = replace(Discount,'-',' ')
DROP TABLE TEMP

Viewing 10 posts - 1 through 9 (of 9 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I don't see a question here... can you explain?

-- Gianluca Sartori

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

try:

UPDATE Temp set Discounturl = replace(replace(replace(Discount,'--','-~'),'-~',''),'-',' ')

EDITED AS THE ABOVE HAS A BUG.

See my next post

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Dohsan SSCrazy Points: 2972 More actions · Answer 3

Having a read of this article should help you:

http://www.sqlservercentral.com/articles/T-SQL/68378/

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Eugene Elutin (6/29/2010)
try:
UPDATE Temp set Discounturl = replace(replace(replace(Discount,'--','-~'),'-~',''),'-',' ')

Doh! Now I see what you mean. Sorry for misunderstanding.

-- Gianluca Sartori

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 5

The UPDATE below will replace the first occurence of -- and any number more dashes in a row, with a single space.

Since you have multiple occurences within the string, you will have to loop the UPDATE 🙁 :

DECLARE @rowcount INT

SET @rowcount = 1

WHILE @rowcount > 0

BEGIN

UPDATE temp

SET discount = STUFF(discount, CHARINDEX('--', discount),

PATINDEX('%[^-]%', SUBSTRING(discount,

CHARINDEX('--', discount) + 2, 100)) + 1, ' ')

WHERE CHARINDEX('--', discount) > 0

SET @rowcount = @@ROWCOUNT

END --WHILE

Scott Pletcher, SQL Server MVP 2008-2010

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

scott.pletcher (6/29/2010)
The UPDATE below will replace the first occurence of -- and any number more dashes in a row, with a single space.
Since you have multiple occurences within the string, you will have to loop the UPDATE 🙁 :
DECLARE @rowcount INT
SET @rowcount = 1
WHILE @rowcount > 0
BEGIN
UPDATE temp
SET discount = STUFF(discount, CHARINDEX('--', discount),
PATINDEX('%[^-]%', SUBSTRING(discount,
CHARINDEX('--', discount) + 2, 100)) + 1, ' ')
WHERE CHARINDEX('--', discount) > 0
SET @rowcount = @@ROWCOUNT
END --WHILE

I don't think you need a loop here.

My first post had some small bug...

Try this one:

UPDATE Temp set Discounturl = replace(replace(replace(replace(Discount,'--','-~'),'~-',''),'~',''),'-',' ')

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 7

True enough; and I saw the other link, which is identical to your method.

Scott Pletcher, SQL Server MVP 2008-2010

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

Dohsan (6/29/2010)

Having a read of this article should help you:

http://www.sqlservercentral.com/articles/T-SQL/68378/%5B/quote%5D

Actually, I have to admit, my really cool (or so I thought :blush: )method got the pants beat off it by simple nested replacements. Check out the following link from the discussion in that article.... make sure you watch your collations on this type of thing, folks...

http://www.sqlservercentral.com/Forums/Topic819042-203-3.aspx#BM820813

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

Jeff Moden (6/29/2010)
...
Actually, I have to admit, my really cool (or so I thought :blush: )method got the pants beat off it by simple nested replacements. Check out the following link from the discussion in that article.... make sure you watch your collations on this type of thing, folks...
http://www.sqlservercentral.com/Forums/Topic819042-203-3.aspx#BM820813

Good 34 pages read :-D.

When loading datawarehouse with millions of rows where performance was a paramount concern, we have used CLR as it was a fasterst option.

Interesting solution with nested replacements. It is fast, however I wouldn't use it due to its "noodle" appearance :-D.

Hornestly, I like your's (and my) OX method over all of others, just because!

I know this method from my Math teacher in primary school, it was one of the logical puzzles she loved...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]