How to replace WHITESPACE (1 or more spaces) in a field with a SINGLE DASH?

MIKE
MIKE used Ask the Experts™
on
My field has data like this:

1P0007.63579.0000                           1609168541          899972053           1230

I need to remove all spaces (1 or more) and replace the blank space with a single dash '-' like this:

1P0007.63579.0000-1609168541-899972053-1230

Can someone tell me what SQL Code to use?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Developer
Commented:
Take a look here: http://sqlmag.com/t-sql/t-sql-string-manipulation-tips-and-techniques-part-1

Especially the paragraph about replacing any number of spaces with a single space. Just modify the last step to create the hyphen:

SELECT REPLACE(REPLACE(REPLACE(yourcolumn, ' ', '~ '), ' ~', ''), '~ ', '-') as newcolumn FROM yourtable

Open in new window


Bye, Olaf.
Shaun KlineLead Software Engineer
Commented:
From different sources, the method to use in SQL server is to use a triple replace. In the first replace, change all double spaces to two characters that are not used in your string, such as {}. In the second replace, change the inverted characters you used, }{, with nothing. In the third replace, change the remaining {} to your desired character. You may also need to insert another replace between the second and third to replace the odd space with nothing.
Shaun KlineLead Software Engineer

Commented:
A similar method could be used with the scripting function in SSRS if you are generating a report.
MIKESoftware Solutions Consultant
Top Expert 2006

Author

Commented:
MANY, MANY THANKS!

Now please see my new question related to this...here:  
https://www.experts-exchange.com/questions/28967659/How-to-CONCANTENATE-ROWS-of-data-into-a-flat-single-ROW.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial