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:

1P0007.63579.0000-1609168541-899972053-1230

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

[Webinar] Streamline your web hosting managementRegister Today

x
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
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.
0
 
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.
0
 
Shaun KlineLead Software EngineerCommented:
A similar method could be used with the scripting function in SSRS if you are generating a report.
0
 
MIKESoftware Solutions ConsultantAuthor 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
0
All Courses

From novice to tech pro — start learning today.