472,982 Members | 2,175 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,982 software developers and data experts.

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 17440

"Joe Cool" <jo*****@home.netwrote 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(REPLACE(REPLACE(RTRIM(LTRI M(@FieldName)),
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******@hotmail.com>
wrote:
>
"Joe Cool" <jo*****@home.netwrote 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(REPLACE(REPLACE(RTRIM(LTRI M(@FieldName)),
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******@hotmail.com>
wrote:
>"Joe Cool" <jo*****@home.netwrote 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(REPLACE(REPLACE(RTRIM(LTRI M(@FieldName)),
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*******@socal.rr.comwrote in message
news:45***********************@roadrunner.com...
Joe Cool wrote:
>On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <ru******@hotmail.com>
wrote:
>>"Joe Cool" <jo*****@home.netwrote 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(REPLACE(REPLACE(RTRIM(LTRI M(@FieldName)),
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*******@socal.rr.comwrote in message
news:45***********************@roadrunner.com...
>Joe Cool wrote:
>>On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <ru******@hotmail.com>
wrote:

"Joe Cool" <jo*****@home.netwrote 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(REPLACE(REPLACE(RTRIM(LTRI M(@FieldName)),
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******@hotmail.comwrote:
>

How did you guys miss the recursion boat?

Create function dbo.udf_CondenseSpaces (@str varchar(8000))

Returns varchar(8000)
AS

BEGIN
Declare @s varchar(8000)

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

if charindex(' ', @s) 0

set @s = dbo.udf_CondenseSpaces(@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(@somestring,' ',' ')
end
print @somestring
---output aaa bbb cccc eee fff f gggg h i aa


"Blackburn" <bl*******@2centsediting.mypants.comwrote in message
news:11************@sp12lax.superfeed.net...
"Russ Rose" <ru******@hotmail.comwrote:
>>


How did you guys miss the recursion boat?

Create function dbo.udf_CondenseSpaces (@str varchar(8000))

Returns varchar(8000)
AS

BEGIN
Declare @s varchar(8000)

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

if charindex(' ', @s) 0

set @s = dbo.udf_CondenseSpaces(@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
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...
7
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
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...
5
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...
1
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
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
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 ...
5
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
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...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.