Remove characters at the end of a string.

I need to clean up a database field and remove any characters at the end of the string that start with a bracket [.

The field has values with 10 characters or more.  I need to cut off at the end of the string any  characters beginning with the [

Example:

ABC-X-1111 is fine
ABC-X-1111 [aaaa] is not. I need to cut off the [aaaa] at the end of the field.
Crystal RouseTask LeadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
This should be a good starting point, assuming you want to update the table in place.

UPDATE table1
SET column1 = TRIM(SUBSTRING(column1, 1, CHARINDEX('[' , column1) - 1))
WHERE CHARINDEX ('[' , column1) > 0;

Open in new window


»bp
Mark WillsTopic AdvisorCommented:
Bill is absolutely correct with having to use CHARINDEX

Check first with

Select YourColumn, LEFT(YourColumn, charindex('[',YourColumn) - 1) as NewColumn 
from YourTable
where charindex('[',YourColumn) > 0

Open in new window


Then if you are happy with the results you can update
update T set YourColumn = LEFT(T.YourColumn, charindex('[',T.YourColumn) - 1)  
from YourTable T
where charindex('[',T.YourColumn) > 0

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuaMin ChenProblem resolverCommented:
Use Charindex to remove part bracketed with [], like
DECLARE @doc varchar(64);  
SELECT @doc = 'Reflectors are vital safety' +  
                   ' components of your bicycle.[abc]';  
SELECT substring(@doc,1,CHARINDEX('[', @doc)-1);  
GO  

Open in new window

Crystal RouseTask LeadAuthor Commented:
Thank you for your answers. This is awesome and I am able to get my update query working correctly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.