|
|
Posted by Hugo Kornelis on 10/22/06 22:21
On 21 Oct 2006 09:05:10 -0700, feucos@gmail.com 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(....REPLACE(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
Navigation:
[Reply to this message]
|