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

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:


Can someone tell me what SQL Code to use?
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
Take a look here:

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 KlineConnect With a Mentor Lead Software EngineerCommented:
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 EngineerCommented:
A similar method could be used with the scripting function in SSRS if you are generating a report.
MIKESoftware Solutions ConsultantAuthor Commented:

Now please see my new question related to
All Courses

From novice to tech pro — start learning today.