Replce SPACES in values

13 Jan 2006 8:26 PM

Mike Kansky

I have a task before me that i need help with.

I have values in table that look like this:
'MY TASK' [random_operator] 'VERY ANNOYING'

I need to replace SPACES that are inside quotes with '_?'
Only values that have quotes around them should have their spaces replaced
with '_?'

Any idea would be appreciated.

13 Jan 2006 8:37 PM

Rick Sawtell

"Mike Kansky" <postmas***@zazasoftware.com> wrote in message
news:OsSxg%23HGGHA.376@TK2MSFTNGP12.phx.gbl...
>I have a task before me that i need help with.
>
> I have values in table that look like this:
> 'MY TASK' [random_operator] 'VERY ANNOYING'
>
> I need to replace SPACES that are inside quotes with '_?'
> Only values that have quotes around them should have their spaces
> replaced with '_?'
>
> Any idea would be appreciated.
>

I'm not sure what you are after here, but using either a REPLACE, or some
type of CHARINDEX / PATINDEX combined with SUBSTRING may be helpful

What is the output you are looking for?

Using your above example, would you be looking to do the following?

'MY_?TASK' [random_operator] 'VERY_?ANNOYING'

Rick Sawtell
MCT, MCSD, MCDBA

13 Jan 2006 8:47 PM

Mike Kansky

the value is:
'MY TASK' [random_operator] 'VERY ANNOYING'

The output i am looking for is
'MY_?TASK' [random_operator] 'VERY_?ANNOYING'

But if i use this:
replace('MY TASK' [random_operator] 'VERY ANNOYING',' ','_?')
the output will be :
'MY_?TASK'_?[random_operator]_?'VERY_?ANNOYING'

I need to replace spaces ONLY in values in quotes.

Show quote

"Rick Sawtell" <Quicken***@msn.com> wrote in message
news:%23YeMIGIGGHA.608@TK2MSFTNGP14.phx.gbl...
>
> "Mike Kansky" <postmas***@zazasoftware.com> wrote in message
> news:OsSxg%23HGGHA.376@TK2MSFTNGP12.phx.gbl...
>>I have a task before me that i need help with.
>>
>> I have values in table that look like this:
>> 'MY TASK' [random_operator] 'VERY ANNOYING'
>>
>> I need to replace SPACES that are inside quotes with '_?'
>> Only values that have quotes around them should have their spaces
>> replaced with '_?'
>>
>> Any idea would be appreciated.
>>
>
> I'm not sure what you are after here, but using either a REPLACE, or some
> type of CHARINDEX / PATINDEX combined with SUBSTRING may be helpful
>
> What is the output you are looking for?
>
> Using your above example, would you be looking to do the following?
>
> 'MY_?TASK' [random_operator] 'VERY_?ANNOYING'
>
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>

13 Jan 2006 9:25 PM

Rick Sawtell

Show quote

"Mike Kansky" <postmas***@zazasoftware.com> wrote in message
news:uv9AIKIGGHA.2680@TK2MSFTNGP09.phx.gbl...
> the value is:
> 'MY TASK' [random_operator] 'VERY ANNOYING'
>
> The output i am looking for is
> 'MY_?TASK' [random_operator] 'VERY_?ANNOYING'
>
> But if i use this:
> replace('MY TASK' [random_operator] 'VERY ANNOYING',' ','_?')
> the output will be :
> 'MY_?TASK'_?[random_operator]_?'VERY_?ANNOYING'
>
> I need to replace spaces ONLY in values in quotes.
>

In SQL2k.. You could try this...

CREATE TABLE #Foo (
col1 varchar(100)
)

INSERT #Foo VALUES ('''MY TASK'' [random_operator] ''VERY ANNOYING''')
INSERT #Foo VALUES ('''MY TASK TWO'' [random_operator] ''Extremely VERY
ANNOYING''')
INSERT #Foo VALUES ('''MY TASK THREE'' [random_operator] ''ANNOYING''')

DECLARE @start int,
@end int,
@data varchar(100),
@chunk varchar(100),
@replace varchar(100)

DECLARE cur CURSOR DYNAMIC FOR
SELECT col1
FROM #Foo
FOR UPDATE OF col1

SELECT @start = 0, @end = 0

OPEN cur

FETCH NEXT
FROM cur
INTO @data

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @start = CHARINDEX('''', @data, 0)
WHILE @start < DATALENGTH(@data) AND @start > 0 -- While loop for multiple
quoted items in a single column
BEGIN

SELECT @end = CHARINDEX('''', @data, @start + 1)
IF @end > 0 -- Continue processing, we have an end quote.
BEGIN
   SET @replace = SUBSTRING(@data, @start, @end - @start + 1) -- Get the
substring between the quotes
   SET @chunk = REPLACE(@replace, ' ', '_?') -- Put _? in place of spaces
   SET @data = REPLACE(@data, @replace, @chunk) -- Update data substring
with fixed data
   SET @end = @start + LEN(@chunk) + 1 -- Get a new starting position past
the end of the last one.
END
SELECT @start = CHARINDEX('''', @data, @end)
END

UPDATE #Foo SET col1 = @data
WHERE CURRENT OF cur

FETCH NEXT
FROM cur
INTO @data

END

CLOSE cur
DEALLOCATE cur

SELECT * FROM #Foo

DROP TABLE #Foo

13 Jan 2006 9:15 PM

William Stacey [MVP]

It is a good job for regex. But I can never remember regex. So here is a
clr function.

-- Test the function.
declare @s nvarchar(50)
set @s = '''' + 'MY TASK' + '''' + ' [random_operator] ' + '''' + 'VERY
ANNOYING' + ''''
select @s, dbo.ReplaceSpacesInQuotes(@s, '_?')

-- The CLR UDF.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

public partial class UserDefinedFunctions
{
    /// <summary>
    /// Replaces any space inside single quote pairs with the supplied
string.
    /// </summary>
    /// <param name="source">The source string.</param>
    /// <param name="replaceWith">The string to replace spaces with.</param>
    /// <returns>A new string.</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString ReplaceSpacesInQuotes(string source, string
replaceWith)
    {
        if (replaceWith == null)
            return source;
        if ( source == null )
            return SqlString.Null;

        bool open = false;
        string sc = "";
        StringBuilder sb = new StringBuilder();
        foreach (char c in source)
        {
            sc = c.ToString();
            if (sc == "'")
                open = !open;
            if (sc == " ")
                if (open)
                    sc = replaceWith;
            sb.Append(sc);
        }
        return sb.ToString();
    }
};

--
William Stacey [MVP]

Show quote

"Mike Kansky" <postmas***@zazasoftware.com> wrote in message
news:OsSxg%23HGGHA.376@TK2MSFTNGP12.phx.gbl...
>I have a task before me that i need help with.
>
> I have values in table that look like this:
> 'MY TASK' [random_operator] 'VERY ANNOYING'
>
> I need to replace SPACES that are inside quotes with '_?'
> Only values that have quotes around them should have their spaces
> replaced with '_?'
>
> Any idea would be appreciated.
>

13 Jan 2006 9:25 PM

Mike Kansky

That will do, thanks! I just converted it to T-SQL function and it works
like a charm!

Show quote

"William Stacey [MVP]" <william.sta***@gmail.com> wrote in message
news:uok%23rZIGGHA.1388@TK2MSFTNGP11.phx.gbl...
> It is a good job for regex. But I can never remember regex. So here is a
> clr function.
>
> -- Test the function.
> declare @s nvarchar(50)
> set @s = '''' + 'MY TASK' + '''' + ' [random_operator] ' + '''' + 'VERY
> ANNOYING' + ''''
> select @s, dbo.ReplaceSpacesInQuotes(@s, '_?')
>
> -- The CLR UDF.
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Text;
>
> public partial class UserDefinedFunctions
> {
>    /// <summary>
>    /// Replaces any space inside single quote pairs with the supplied
> string.
>    /// </summary>
>    /// <param name="source">The source string.</param>
>    /// <param name="replaceWith">The string to replace spaces
> with.</param>
>    /// <returns>A new string.</returns>
>    [Microsoft.SqlServer.Server.SqlFunction]
>    public static SqlString ReplaceSpacesInQuotes(string source, string
> replaceWith)
>    {
>        if (replaceWith == null)
>            return source;
>        if ( source == null )
>            return SqlString.Null;
>
>        bool open = false;
>        string sc = "";
>        StringBuilder sb = new StringBuilder();
>        foreach (char c in source)
>        {
>            sc = c.ToString();
>            if (sc == "'")
>                open = !open;
>            if (sc == " ")
>                if (open)
>                    sc = replaceWith;
>            sb.Append(sc);
>        }
>        return sb.ToString();
>    }
> };
>
> --
> William Stacey [MVP]
>
> "Mike Kansky" <postmas***@zazasoftware.com> wrote in message
> news:OsSxg%23HGGHA.376@TK2MSFTNGP12.phx.gbl...
>>I have a task before me that i need help with.
>>
>> I have values in table that look like this:
>> 'MY TASK' [random_operator] 'VERY ANNOYING'
>>
>> I need to replace SPACES that are inside quotes with '_?'
>> Only values that have quotes around them should have their spaces
>> replaced with '_?'
>>
>> Any idea would be appreciated.
>>
>
>