| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2009-01-31 : 03:04:49
|
| Hi there I have a data like this: "931 WHITFORDS AVE WOODVALE"and would like to remove "some space" between AVE and WOODVALE and become to: "931 WHITFORDS AVE WOODVALE"Any ideas? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-31 : 03:34:25
|
You mean the 20 spaces between "ave" and "woodvale"?REPLACE(Col1, char(32) + char(32), char(32))
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2009-01-31 : 14:38:17
|
quote:
Originally posted by Peso
You mean the 20 spaces between "ave" and "woodvale"?REPLACE(Col1, char(32) + char(32), char(32))
E 12°55'05.63"N 56°04'39.26"
Actually ... sorry the space was truncated in this forum. The original data suppose to be:"931[SPACE]WHITFORDS[SPACE]AVE[SPACE][SPACE][SPACE][SPACE][SPACE][SPACE][SPACE][SPACE]WOODVALE"I want the result become like this (removing some [SPACE] between AVE and WOOVALE."931[SPACE]WHITFORDS[SPACE]AVE[SPACE]WOODVALE"Basically, I want to remove [SPACE] more than 1 between words. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-01-31 : 19:01:25
|
There is no cool way to do this in T-SQL.Maybe you can can use regular expressions in a CLR user-defined function.The ugly way is to use nested replace-statements.WebfredPS: Maybe you have a better way in your front end?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-02-02 : 11:41:32
|
Just because it's ugly doesn't mean you shouldn't use it to make your data more readable/valid. Here's what should work:DECLARE @t TABLE(string_col VARCHAR(100))INSERT INTO @tSELECT '931 WHITFORDS AVE WOODVALE' UNION ALLSELECT 'a b c' UNION ALLSELECT 'a b c' UNION ALLSELECT 'a b c'SELECT 'a b c 'SELECT string_col,REPLACE(REPLACE(REPLACE(string_col,' ',' ~!#$^'),'~!#$^ ',''),'~!#$^','') AS new_string_colFROM @t Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-02-02 : 11:43:50
|
| The forum auto-formats my post to remove the extra spaces, but the code should still work. You can also get my original formatting by clicking "Reply with quote" and copy pasting it from there. Good luck!Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-02-02 : 15:53:56
|
quote:
Originally posted by Skorch
The forum auto-formats my post to remove the extra spaces, but the code should still work. You can also get my original formatting by clicking "Reply with quote" and copy pasting it from there. Good luck!Some days you're the dog, and some days you're the fire hydrant.
Have you tried with [ code ] a b c [ /code ] (without extra spaces around code /code)?Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-02-02 : 17:57:41
|
quote:
Originally posted by webfred
quote:
Originally posted by Skorch
The forum auto-formats my post to remove the extra spaces, but the code should still work. You can also get my original formatting by clicking "Reply with quote" and copy pasting it from there. Good luck!Some days you're the dog, and some days you're the fire hydrant.
Have you tried with [ code ] a b c [ /code ] (without extra spaces around code /code)?Webfred
No, you're never too old to Yak'n'Roll if you're too young to die.
Thanks! I didn't know about the code tags here.Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
|
Removing Space