Issue with replace

Transact-SQL
1

Hello here's my query:

UPDATE update
SET     Artist = REPLACE(Artist,' (','-')
WHERE (Artist LIKE '%([0-9][0-9][0-9][0-9])%')

Which works, but some times there's multiple things with parentheses. So how can I get it to update anything with parentheses and a year (Artist LIKE '%([0-9][0-9][0-9][0-9])%'), but leave anything else with a parentheses alone. So if the artist field looks like this:

TRANSFORMERS (2007) (WS DUB SUB AC3 DOL THD)

it would get updated to look like this:

TRANSFORMERS-2007 (WS DUB SUB AC3 DOL THD)

Thanks

:

2

SQL Server does not have support for regex except through CLR functions. So this is somewhat convoluted, but give it a shot. I am showing with a single variable, you can easily adapt it to data in a table by replacing the variable with the column name.

DECLARE @x VARCHAR(256) = 'TRANSFORMERS (2007) (WS DUB SUB AC3 DOL THD)';
SELECT STUFF(@x,
	PATINDEX('%([0-9][0-9][0-9][0-9])%',@x),
	6,'-'+SUBSTRING(@x,PATINDEX('%([0-9][0-9][0-9][0-9])%',@x)+1,4)
	);
3

Yep, issues is that was just one case. I would need it to look for anything with a parentheses and year and replace the beginning ' (' with '-' and remove the ending ')'. So it would look for '%([0-9][0-9][0-9][0-9])%' and update it to '-1997' or whatever it is.. It doesn't have to use replace, if there's a better way to do that I would be up for that.

Thanks

4

What I posted will do what you are describing. It does not have to be 2007. As long as it is a four-digit number within parenthesis, it should work.

5

Sorry I got you now, the only thing is it's adding a space at the end. Here's my coding:

SELECT Title, STUFF(Title, PATINDEX('%([0-9][0-9][0-9][0-9])%', Title), 6, '-' + SUBSTRING(Title,       PATINDEX('%([0-9][0-9][0-9][0-9])%', Title) + 1, 4)) AS Expr1
FROM  update
WHERE (Title LIKE '%([0-9][0-9][0-9][0-9])%')

and it returning TRANSFORMERS -2007 (WS DUB SUB AC3 DOL THD). Where's the space comming from?

Thanks

6

Do you mean the space between TRANSFORMERS and the -2007?

SELECT Title, STUFF(Title, PATINDEX('% ([0-9][0-9][0-9][0-9])%', Title), 7, '-' + 
	SUBSTRING(Title,       PATINDEX('%([0-9][0-9][0-9][0-9])%', Title) + 1, 4)) AS Expr1
7

Yep the space between the two. The code above worked like a charm.

Thanks!

8

As a bit of a sidebar, the data in your example row is horribly denormalized. You wouldn't have these problems if the data were normalized.

9

Might be an Import process? This sort of data un-mangling is normal for us when we get data from Clients' other "so called normalized databases" ... I don't suppose our database structure is perfect, but it amazes me what we are presented with from other vendor solutions - even Big Name Brand applications and their databases.

I could tell them a thing or two, I can tell you ...