|
|
Posted by Ed Murphy on 02/19/07 21:44
Russ Rose wrote:
> "Ed Murphy" <emurphy42@socal.rr.com> wrote in message
> news:45d51798$0$28101$4c368faf@roadrunner.com...
>> Joe Cool wrote:
>>
>>> On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <russrose@hotmail.com>
>>> wrote:
>>>
>>>> "Joe Cool" <joecool@home.net> wrote in message
>>>> news:5pb7t2dkt37mgh0cqnm3f9luuu1rvhglkm@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(LTRIM(@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).
Navigation:
[Reply to this message]
|