Remove long spaces in middle of string 1

[bmacbmac]

Hi. I have data in a table like this:

NW 35 6 6 55 PLAT ID 111 T176 BERKS


I'd like to remove the long strings of spaces in the middle of this string, but I dont' want to remove the single space between words.

I thought about just replacing double spaces with single spaces over and over again until there were no more double spaces left:

set field1 = replace (field1, '  ', ' ')

I'm wondering if there is a more efficient way to do this.

Thanks!

 

[bborissov]

You can use several replaces at once, but you always should worry if double space count will exceed the number of replaces.
You can write function that will remove these double spaces but that way you can have performance issues.
Just google "remove double space tsql"

Borislav Borissov
VFP9 SP2, SQL Server

 

[Andrzejek]

You can loop and see if you have any two-spaces in a field and replace them with one space, but...
1. Why do you want to do this? Just for text to look nice? Or is there any other reason to do so?
2. Do you have multi-value field and you want to retrieve a part of it? And that's why you need to 'format' the data?


---- Andy

There is a great need for a sarcasm font.

 

[gmmastros]

If you are going to update/fix the data, then performance is probably not terribly important.

I would suggest something like this...

While Exists(Select 1 From YourTable Where field1 Like '%[ ][ ]%')
  Begin
    Update  YourTable
    Set     Field1 = replace(field1, '  ', ' ')
    Where   Field1 Like '%[ ][ ]%'
  End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Thanks gmmastros. I like that idea...

Out of curiosity, is there a reason to use the brackets? Does this: like '%[ ][ ]%' do the same thing as: '% %'? Or was that just for illustration?

 

[simian336]

The brackets indicated it is a Regular Expression.

Simi

 

[gmmastros]

bmacbmac, it probably is the same. When posting, and reading the code, it's hard to determine the difference between a single space and 2 spaces. Therefore, it's more obvious that we're searching for 2 spaces, which makes for better code.

simian336, there is no means for regular expressions in T-SQL. The only way to truly do regular expressions in sql is to use a CLR. For something as simple as looking for 2 spaces, CLR would be much slower.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Thanks George...

Simi