473,735 Members | 2,093 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Replace Multiple Spaces with One Space?

I need to map several columns of data from one database to another
where the data contains multiple spaces (once occurance of a variable
number or spaces) that I need to replace with a single space. What
would be the most efficient way to do this? I am using SQL2K. I was
thinking a function since I know of no single Transact-SQL command
that can accomplish this task.
Feb 15 '07 #1
8 17512

"Joe Cool" <jo*****@home.n etwrote in message
news:5p******** *************** *********@4ax.c om...
>I need to map several columns of data from one database to another
where the data contains multiple spaces (once occurance of a variable
number or spaces) that I need to replace with a single space. What
would be the most efficient way to do this? I am using SQL2K. I was
thinking a function since I know of no single Transact-SQL command
that can accomplish this task.
DECLARE @FieldName varchar(5000)
SET @FieldName = ' Reduce any number of' + SPACE(512) +
'spaces up to 512 in
a row to a single space
'

--You can reduce layers of REPLACE depending how many extra spaces you
expect.

SELECT REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(RTRIM (LTRIM(@FieldNa me)),
SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '), SPACE(2), '
')
Feb 15 '07 #2
On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <ru******@hotma il.com>
wrote:
>
"Joe Cool" <jo*****@home.n etwrote in message
news:5p******* *************** **********@4ax. com...
>>I need to map several columns of data from one database to another
where the data contains multiple spaces (once occurance of a variable
number or spaces) that I need to replace with a single space. What
would be the most efficient way to do this? I am using SQL2K. I was
thinking a function since I know of no single Transact-SQL command
that can accomplish this task.

DECLARE @FieldName varchar(5000)
SET @FieldName = ' Reduce any number of' + SPACE(512) +
'spaces up to 512 in
a row to a single space
'

--You can reduce layers of REPLACE depending how many extra spaces you
expect.

SELECT REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(RTRIM (LTRIM(@FieldNa me)),
SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '), SPACE(2), '
')
I believe you misunderstood my question. Here are some sample values:

"SMITH JR" (5 spaces)
"JONES JR" (7 spaces)

Desired result"

"SMITH JR"
"JONES JR"

Feb 16 '07 #3
Joe Cool wrote:
On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <ru******@hotma il.com>
wrote:
>"Joe Cool" <jo*****@home.n etwrote in message
news:5p******* *************** **********@4ax. com...
>>I need to map several columns of data from one database to another
where the data contains multiple spaces (once occurance of a variable
number or spaces) that I need to replace with a single space. What
would be the most efficient way to do this? I am using SQL2K. I was
thinking a function since I know of no single Transact-SQL command
that can accomplish this task.
DECLARE @FieldName varchar(5000)
SET @FieldName = ' Reduce any number of' + SPACE(512) +
'spaces up to 512 in
a row to a single space
'

--You can reduce layers of REPLACE depending how many extra spaces you
expect.

SELECT REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(RTRIM (LTRIM(@FieldNa me)),
SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '), SPACE(2), '
')

I believe you misunderstood my question. Here are some sample values:

"SMITH JR" (5 spaces)
"JONES JR" (7 spaces)

Desired result"

"SMITH JR"
"JONES JR"
Looks to me like his answer produces the desired result. What problem
do you see with it?
Feb 16 '07 #4

"Ed Murphy" <em*******@soca l.rr.comwrote in message
news:45******** *************** @roadrunner.com ...
Joe Cool wrote:
>On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <ru******@hotma il.com>
wrote:
>>"Joe Cool" <jo*****@home.n etwrote in message
news:5p****** *************** ***********@4ax .com...
I need to map several columns of data from one database to another
where the data contains multiple spaces (once occurance of a variable
number or spaces) that I need to replace with a single space. What
would be the most efficient way to do this? I am using SQL2K. I was
thinking a function since I know of no single Transact-SQL command
that can accomplish this task.
DECLARE @FieldName varchar(5000)
SET @FieldName = ' Reduce any number of' + SPACE(512) +
'spaces up to 512 in a
row to a single space '

--You can reduce layers of REPLACE depending how many extra spaces you
expect.

SELECT REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(RTRIM (LTRIM(@FieldNa me)),
SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '),
SPACE(2), ' ')

I believe you misunderstood my question. Here are some sample values:

"SMITH JR" (5 spaces)
"JONES JR" (7 spaces)

Desired result"

"SMITH JR"
"JONES JR"

Looks to me like his answer produces the desired result. What problem
do you see with it?
It was a bit of overkill... maybe this will be closer to what you want.
CREATE FUNCTION dbo.SingleSpace (@str varchar(8000))

RETURNS varchar(8000)

AS
--Usage
--SELECT dbo.SingleSpace ('Function replaces any and all spaces up
to 16 in a row with a single space ')

BEGIN
DECLARE @s varchar(8000)
SELECT @s = REPLACE(REPLACE (REPLACE(RTRIM( LTRIM(@str)), SPACE(4), ' '),
SPACE(2), ' '), SPACE(2), ' ')
RETURN @s
END
Feb 17 '07 #5
Russ Rose wrote:
"Ed Murphy" <em*******@soca l.rr.comwrote in message
news:45******** *************** @roadrunner.com ...
>Joe Cool wrote:
>>On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <ru******@hotma il.com>
wrote:

"Joe Cool" <jo*****@home.n etwrote in message
news:5p***** *************** ************@4a x.com...
I need to map several columns of data from one database to another
where the data contains multiple spaces (once occurance of a variable
number or spaces) that I need to replace with a single space. What
would be the most efficient way to do this? I am using SQL2K. I was
thinking a function since I know of no single Transact-SQL command
that can accomplish this task.
DECLARE @FieldName varchar(5000)
SET @FieldName = ' Reduce any number of' + SPACE(512) +
'spaces up to 512 in a
row to a single space '

--You can reduce layers of REPLACE depending how many extra spaces you
expect.

SELECT REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(RTRIM (LTRIM(@FieldNa me)),
SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '),
SPACE(2), ' ')
I believe you misunderstood my question. Here are some sample values:

"SMITH JR" (5 spaces)
"JONES JR" (7 spaces)

Desired result"

"SMITH JR"
"JONES JR"
Looks to me like his answer produces the desired result. What problem
do you see with it?

It was a bit of overkill... maybe this will be closer to what you want.
CREATE FUNCTION dbo.SingleSpace (@str varchar(8000))

RETURNS varchar(8000)

AS
--Usage
--SELECT dbo.SingleSpace ('Function replaces any and all spaces up
to 16 in a row with a single space ')

BEGIN
DECLARE @s varchar(8000)
SELECT @s = REPLACE(REPLACE (REPLACE(RTRIM( LTRIM(@str)), SPACE(4), ' '),
SPACE(2), ' '), SPACE(2), ' ')
RETURN @s
END
Doesn't work on 11, 14, or 15 spaces.

Adding an extra REPLACE(..., SPACE(2), ' ') wrapper will fix it, and
extend it to runs up to 26.

Adding an inner 8->1 on top of that extends to 167; 16->1 on top of that
extends to 2447; 32->1 on top of that extends to 77343, more than enough
unless you're messing with TEXT (for which REPLACE doesn't work at all,
IIRC) or VARCHAR(MAX).
Feb 19 '07 #6
Do an UPDATE statement with nested REPLACE() functions to change (n)
spaces to 1 space:

UPDATE Foobar
SET mystring = REPLACE (SPACE(2), SPACE(1) ' ,
...
REPLACE (SPACE(<<fib(n) >>), SPACE(1),
mystring)
.. )));

The optimal pattern for the substitutions is a Fibbonnaci series with
the longest string of spaces in the innermost invocation. You have to
pick the right number based on the length of the column. Working out
the math is fun, so enjoy.

Feb 20 '07 #7
"Russ Rose" <ru******@hotma il.comwrote:
>

How did you guys miss the recursion boat?

Create function dbo.udf_Condens eSpaces (@str varchar(8000))

Returns varchar(8000)
AS

BEGIN
Declare @s varchar(8000)

Set @s = replace(@str, ' ', ' ')

if charindex(' ', @s) 0

set @s = dbo.udf_Condens eSpaces(@s)

return @s
END
Oct 18 '07 #8
also instead of calling a function many times
create a function or a proct
Sample incomming string

declare @somestring varchar(100)
set @somestring = 'aaa bbb cccc eee fff f gggg h i aa '
--sample guts of function / proc
while charindex(' ',@somestring)> 0 begin
set @Somestring = replace(@somest ring,' ',' ')
end
print @somestring
---output aaa bbb cccc eee fff f gggg h i aa


"Blackburn" <bl*******@2cen tsediting.mypan ts.comwrote in message
news:11******** ****@sp12lax.su perfeed.net...
"Russ Rose" <ru******@hotma il.comwrote:
>>


How did you guys miss the recursion boat?

Create function dbo.udf_Condens eSpaces (@str varchar(8000))

Returns varchar(8000)
AS

BEGIN
Declare @s varchar(8000)

Set @s = replace(@str, ' ', ' ')

if charindex(' ', @s) 0

set @s = dbo.udf_Condens eSpaces(@s)

return @s
END

Oct 18 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3087
by: Bill Mittenzwey | last post by:
Is there a way to do a Regex replace on a string to stuff some variable amount of spaces into a location of a string? I need an expression which would take an unknown length string and move the last char to position 15. EX: 123456 transforms to 12345 6
7
35902
by: Mindy Geac | last post by:
Is it possible to delete special caracters from a string and multiple spaces? When the input is : "a&*bbb cc/c d!d" I want the result : "abbb ccc dd" thnx. MJ <?php
2
5106
by: José Joye | last post by:
Hello, I was wondering if there is a method that exists to replace multi-spaces within a string with single-space. eg: "12 3 4 56" --> "12 3 4 56" I think this could be done by looking at each char within a loop and copying the char to a stringBuilder instance if current and previous char are not spaces...
5
22938
by: Christine | last post by:
I have a text file that appears to be delimited by multiple spaces. The split function will only work with one space if I am correct. Is there some way to split this file into an array without getting the extra spaces?
1
4085
by: Anonieko Ramos | last post by:
> > > How to display multiple spaces in a dropdownlist webform1.aspx <asp:DropDownList id="DropDownList1" runat="server"></asp:DropDownList>
1
1665
by: Vikram | last post by:
I have a label control whose text is having multile spaces "Name :" but when this is rendered as html in an aspx page it only shows single space. How to render with multiple spaces.
4
2999
by: cyberdrugs | last post by:
Hi guys 'n gals, I have a string which contains multiple spaces, and I would like to convert the multiple spaces into single spaces. Example Input: the quick brown fox jumps over the lazy dog Example Output: the quick brown fox jumps over the lazy dog
5
6371
by: polturgiest | last post by:
hie all i got a form <form name="TEST" method=POST action="test.php"> <input type="text" name="MyInput"> <input type="submit" name="ACTION" value="SAVE">
2
2017
by: rjoseph | last post by:
Hi Guys I hope this is a simple one for you. I am basically displaying data onto my xml page using the following line of code: <xsl:value-of select="carmanufacturer" /> An example of the resulting output would be, "Ford"
0
9462
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9248
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9199
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8199
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6747
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6049
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3270
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2738
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2187
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.