Move anything with a parentheses to another column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Move anything with a parentheses to another column

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2015-05-01 : 15:41:23

Hello, I'm trying to move from my database in the title column into the info column where there's anything with a parentheses () in it. Some examples are:

SMETANA: MOLDAU (JPN) (SHM)
DEEDEE FOSTER (EP)

and would need it to make it look as so:
title info
SMETANA: MOLDAU (JPN) (SHM)
DEEDEE FOSTER (EP)

So what would the best way be to do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-01 : 15:42:26

I'm not seeing how they are separated in your post. Where is the cutoff for title?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2015-05-02 : 02:57:31

There are some spaces in the O/P's post but no [CODE] tags ...

Some examples are:

SMETANA: MOLDAU (JPN) (SHM)
DEEDEE FOSTER (EP)

and would need it to make it look as so:
title			info
SMETANA: MOLDAU		(JPN) (SHM)
DEEDEE FOSTER		(EP)

taunt
Posting Yak Master

128 Posts

Posted - 2015-05-04 : 16:51:12

Well they're in different spots all within the title field. I didn't know if there was a way to have SQL look for text within any parentheses and move it to the info column. I guess the ideal thins would have it move the test within the parentheses and then remove the parentheses after. So it would look like this:

title info
SMETANA: MOLDAU JPN-SHM
DEEDEE FOSTER EP

instead of:

title info
SMETANA: MOLDAU (JPN) (SHM)
DEEDEE FOSTER (EP)

I know how to replace the parentheses, I don't know if there's a way to have it look for anything with a parentheses and move it to the info column. If need be I could have it replace ) ( with - to eliminate the doubled parentheses. Then it could look for title like ('%(%)%') and move that information to the info column.

quote:
Originally posted by tkizer

I'm not seeing how they are separated in your post. Where is the cutoff for title?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-04 : 21:29:21

How bout a regex CLR?

Gerald Britton, MCSA
Toronto PASS Chapter

Kristen
Test

22859 Posts

Posted - 2015-05-05 : 09:36:26

Find the first parenthesis (using CHARINDEX() ) and split the field into two based on that character position

If parenthesis not required in the second column then replace them with nothing

Does it sound like that would do?

taunt
Posting Yak Master

128 Posts

Posted - 2015-05-05 : 15:32:11

OK I got it to work using this:

UPDATE database
SET info = i.Expr1
FROM (SELECT REPLACE(STUFF(CONVERT(nvarchar(30), Title), 1, CHARINDEX('(', CONVERT(nvarchar(30), Title)), ''), ')', '') AS Expr1, PID
FROM database AS database_1) AS i CROSS JOIN
database
WHERE (database.Title LIKE '%(%)%') AND (database.PID = i.PID)

So I guess the next question is, how do I get it to replace anything where titles like (%) with ''? I tried the replace command, but it doesn't work with wildcards. So now I'm stumped on updating the title field by deleting anything in the title field with (text) with nothing '', but leaving the text outside the parenthese alone.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-05 : 15:55:17

What's the cross join for?

Why not just:


update database
set info = Expr1
from database
cross apply (SELECT REPLACE(STUFF(CONVERT(nvarchar(30), Title), 1, CHARINDEX('(', CONVERT(nvarchar(30), Title)), ''), ')', '') _(Expr1) 
WHERE (database.Title LIKE '%(%)%')

Gerald Britton, MCSA
Toronto PASS Chapter

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 03:59:22

Curious why

CONVERT(nvarchar(30), Title)

rather than just using Title directly?

Can't you just do something like this


CREATE TABLE #TEMP
(
	Orig	varchar(30),
	Title	varchar(30),
	Info	varchar(30)
)

INSERT INTO #TEMP
(
	Orig
)
SELECT	'SMETANA: MOLDAU (JPN) (SHM)' UNION ALL
SELECT	'DEEDEE FOSTER (EP)' UNION ALL
SELECT	'EDGE_CONDITION ()' UNION ALL
SELECT	'EDGE_CONDITION (' UNION ALL
SELECT	'(EDGE_CONDITION'

DECLARE	@intOffset	int

UPDATE	U
SET	@intOffset = CHARINDEX('(', Orig),
	Title = RTrim(LEFT(Orig, @intOffset-1)),
	Info = LTrim(RTrim(
			REPLACE(REPLACE(
				SUBSTRING(Orig, @intOffset+1, LEN(Orig))
				, '(', '')
				, ')', '')
		))
FROM	#TEMP AS U
WHERE	    Orig LIKE '%(%'

-- After
SELECT	*
FROM	#TEMP
GO

DROP TABLE #TEMP
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources