CODEWITHSUNDEEP
sql-server
Brian Benham
Brian Benham

Reputation: 69

removing text inside of parentheses in sql then insert that text to different table?

I have a column in MS-SQL that contain addresses the users added some additional information pertaining to the address in parentheses. sometimes it is at the end of the address sometimes it is in the middle. I need to remove that text is there a way to remove the text inside the parentheses and then insert it into another table?

for example the text would be 

58795 North Test Drive (Main Office)

or it could look like this

450 South green Avenue (Green Ave. Office)  Suite 200

Upvotes: 1

Views: 5663

Answers (2)

Brian Benham
Brian Benham

Reputation: 69

I Figured it out.

SELECT Replace(Stuff(CONVERT(VARCHAR (max), metavalue), 1, Charindex('(',    CONVERT(VARCHAR     (max), metavalue)), ''), ')', '')
FROM   metavalues mv
WHERE  mv.subfield = 3007

Upvotes: 1

DeanG
DeanG

Reputation: 647

Assuming exactly one open paren, and exactly one closing paren, then a simple solution exists. I use T1 for the original table name, and T2 for the new table.

BEGIN TRANSACTION

INSERT INTO T2
SELECT ID, SUBSTRING([Address], CHARINDEX('(', [Address]) + 1, CHARINDEX(')', [Address]) - CHARINDEX('(', [Address]) - 1)
FROM T1
WHERE CHARINDEX('(', [Address]) > 0
AND CHARINDEX(')', [Address]) > 0

UPDATE T1
SET [Address] = SUBSTRING([Address], 0, CHARINDEX('(', [Address]))
              + SUBSTRING([Address], CHARINDEX(')', [Address]) + 1, LEN([Address]))

--Check your results

COMMIT TRANSACTION

Upvotes: 5

Related Questions