question

Shivu avatar image
Shivu asked

Trimming data

I'm importing data from a table and the data in one of the columns is in parenthesis. How do I get this data without the parentheses?
t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
AdaTheDev avatar image
AdaTheDev answered
A simple approach, assuming that if there are other parentheses inside the values, you just want to remove all parentheses from the values: DECLARE @Data TABLE (Col1 VARCHAR(100)) INSERT @Data VALUES ('') INSERT @Data VALUES (NULL) INSERT @Data VALUES ('(No ending bracket') INSERT @Data VALUES ('No starting bracket)') INSERT @Data VALUES ('(all brackets)') INSERT @Data VALUES ('(value with (brackets) in middle)') SELECT REPLACE(REPLACE(Col1, '(', ''), ')', '') FROM @Data **Results:** NULL No ending bracket No starting bracket all brackets value with brackets in middle If the values could contain parentheses midway through and you want to preserve those and ONLY remove those that wrap the entire text, then one way is: SELECT CASE WHEN LEFT(Col1, 1) = '(' AND RIGHT(Col1, 1) = ')' THEN SUBSTRING(Col1, 2, LEN(Col1) - 2) WHEN LEFT(Col1, 1) = '(' THEN SUBSTRING(Col1, 2, LEN(Col1) - 1) WHEN RIGHT(Col1, 1) = ')' THEN SUBSTRING(Col1, 1, LEN(Col1) - 1) ELSE COl1 END FROM @Data **Results:** NULL No ending bracket No starting bracket all brackets value with (brackets) in middle **Update:** Re: removing anything to the right of a ")", try this: SELECT CASE WHEN LEFT(Col1, 1) = '(' AND CHARINDEX(')', Col1) > 0 THEN SUBSTRING(Col1, 2, CHARINDEX(')', Col1) - 2) WHEN LEFT(Col1, 1) = '(' THEN SUBSTRING(Col1, 2, LEN(Col1) - 1) WHEN CHARINDEX(')', Col1) > 0 THEN SUBSTRING(Col1, 2, CHARINDEX(')', Col1) - 2) ELSE COl1 END FROM @Data
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shivu avatar image Shivu commented ·
Thanks a lot! Is there a way to remove everything to the right of parentheses? Eg. abc)wxy should be abc Thanks again!
0 Likes 0 ·
AdaTheDev avatar image AdaTheDev commented ·
@Shivu - see the latest update in my answer, which should do that for you
0 Likes 0 ·
Shivu avatar image Shivu commented ·
Got it. Thank you! :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.