Need your help to remove spaces in the column entries using SQL

[feucos]

Hi all,

I am new to these so plz never mind if this is funny.

here is my problem :

Table : moody

Column : Title

New column : Nospace

I have data in "Title" column of many rows which are normal sentence.

My requirment is to remove the "white space", +, | , ., / , ! @, $, %
etc special characters and fill it by ( hyphen) and put it in new
"Nospace" Column

Example :

I have : Hurray ! I won the Game

Needed : Hurray-I-won-the-Game

Can any body helpme in getting an SQL Query for this if possible

Thanks in Advance

[Roy Harvey]

Make a list of all the "white space" characters.

Use REPLACE to change each of them to the dash character. You could
nest them, but it might be simpler to loop through them, changing one
white space character in each UPDATE.

Then reduce multiple dashes to one by using REPLACE(whateve r, '--',
'-') until no more rows are updated.

Roy

On 21 Oct 2006 09:05:10 -0700, fe****@gmail.co m wrote:

>Hi all,

I am new to these so plz never mind if this is funny.

here is my problem :

Table : moody

Column : Title

New column : Nospace

I have data in "Title" column of many rows which are normal sentence.

My requirment is to remove the "white space", +, | , ., / , ! @, $, %
etc special characters and fill it by ( hyphen) and put it in new
"Nospace" Column

Example :

I have : Hurray ! I won the Game

Needed : Hurray-I-won-the-Game

Can any body helpme in getting an SQL Query for this if possible

Thanks in Advance

[--CELKO--]

Nesting the function calls is better because it gets done with ONE
update statement instead several.

[Roy Harvey]

On 22 Oct 2006 05:12:00 -0700, "--CELKO--" <jc*******@eart hlink.net>
wrote:

>Nesting the function calls is better because it gets done with ONE
update statement instead several.

That may very well prove to be an important advantage.

On the other hand, if you accept replacing them one at a time you
could put the characters to be replaced in a table. An advantage to
that would be that adding or subtracting from the set of characters
would be a simple INSERT or DELETE transaction. When nesting changes
to the list requires coding changes.

So, as so often is the case, It Depends.

Roy Harvey
Beacon Falls, CT

[Hugo Kornelis]

On 21 Oct 2006 09:05:10 -0700, fe****@gmail.co m wrote:

(snip)

>My requirment is to remove the "white space", +, | , ., / , ! @, $, %
etc special characters and fill it by ( hyphen) and put it in new
"Nospace" Column

Example :

I have : Hurray ! I won the Game

Needed : Hurray-I-won-the-Game

Can any body helpme in getting an SQL Query for this if possible

Hi feucos,

This is actually quite hard to achieve in straight SQL. Replacing the
various special characters with hyphens is easy, using a nested REPLACE
function - but that would leave you with 'Hurray---I-won-the-Game'.
Removing double hyphens is a lot harder, since there is no maximum
number of hyphens.

If you're on SQL Server 2005, I'd use a CLR user-defined function. Using
CLR means that yoou can use the power of regular expressions to do the
search and replace as quickly as possible.

For SQL Server 2000, you'll either have to use a T-SQL user-defined
function to loop over the characters in the string (but that will be
very slow), or use an awfully ugly but probably lots faster nested
REPLACE function like this:

REPLACE(REPLACE (REPLACE(....RE PLACE(Title, ' ', '-'), '+', '-'), '|',
'-'), ......, '%', '-'), '---------', '-'), '-----', '-'), '---', '-'),
'--', '-')

This will handle series of up to 16 whitespace/special characters. Fill
in the appropriate amount of "REPLACE(" on the first series of dots, and
add "'#', '-')" for each special character to be replaced on the second
series of dots.

--
Hugo Kornelis, SQL Server MVP