Return specified string in "( )" to specified position in a string

[benjatado]

I have several SQL rows of varchar data that have articles such as (A), (The) etc. placed at the end of the string.
ex:
"String Mover (The)"

However, I would like to have this displayed as

"The String Mover"

However, a general rule of anything in () to the front of the string will not always work because many times other information such as dates and context information appear within the parenthesis.
ex:
Walk in the Park (A) (unabridged)
to
A Walk in the Park (unabridged)
AND
Walkers' Journal: Walk in the Park (A) (unabridged)
to
Walkers' Journal: A Walk in the Park (unabridged)

Is there a function(s) that will allow me to move specific articles that appear within parenthesis to the front/or a specified position within a string (such as after the ":" as above)?

Any help would be appreciated!

Thanks!

 

[DotNetGnat]

are these the only 3 different formats you are going to encounter or would there be many more unexpected ones...

you need to write a custom function after concluding all the possible combinations...

-DNG

 

[benjatado]

DNG-

Thanks for the reply!

Yes these, and possibly 5 others

How would I go about writing a custom function within SQL2000?
Sounds cool though!

 

[amrita418]

Is this a one time update? If yes, using charindex and replace should do it.

Sample code:

declare 
@stringval varchar(50)
set    @stringval = 'Walk in the Park (A) (unabridged)'
select @stringval,  
       replace(stuff(@stringval, 1, 0, 'A '), '(A) ', '')

The code below should take care of one of your cases, you can build the rest in the same lines:

update tableA
set    stringval = replace(stuff(stringval, 1, 0, 'A '), '(A) ', '')
where  charindex('(A) ', stringval) != 0

Regards,
AA