Brtdku

I want to split the strings in the 'Scorer' column so that the scorer name is retained but not the score type (i.e. to remove the text within the brackets and the brackets to just leave the scorer name in that field).

Scorer

Ellis J.(Conversion Goal)

Ellis J.(Try)

Ellis J.(Conversion Goal)

Trueman J.(Try)

(Conversion Goal)Brough D.

(Try)McGillvary J.

(Try)McGillvary J.

(Penalty Goal)Brough D.

Ellis J.(Conversion Goal)

It should look like the below.

Scorer

Ellis J.

Ellis J.

Ellis J.

Trueman J.

Brough D.

McGillvary J.

McGillvary J.

Brough D.

Ellis J.

The correct solution would be to fix the database structure by adding another column to the table for the score type. In fact, you should probably have a table for score types and add a foreign key to it from this table.

Assuming you can't change the database structure, this is better done at the presentation layer. Any programming language should enable you do do it quite easily. String manipulation is not SQL's strong suit.

That being said, it can certainly be done using pure T-SQL - with a simple common table expression to get the brackets indexes using charindex, and a case expression with stuff in the select statement.

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE

(

    Scorer nvarchar(100)

);



INSERT INTO @T (Scorer) VALUES

('Ellis J.(Conversion Goal)'),

('Ellis J.(Try)'),

('Ellis J.(Conversion Goal)'),

('Trueman J.(Try)'),

('(Conversion Goal)Brough D.'),

('(Try)McGillvary J.'),

('(Try)McGillvary J.'),

('(Penalty Goal)Brough D.'),

('Ellis J.(Conversion Goal)'),

-- Note: I've added some edge cases to the sample data:

('a row with (brackets) in the middle'),

('Just an open bracket (forgot to close '),

('Just a close bracket forgot to open)'),

('no brackets at all'),

('brackets ) in reversed order (');

Then, the CTE:

WITH CTE AS

(

    SELECT  Scorer, 

            CHARINDEX('(', Scorer) As OpenBrackets,

            CHARINDEX(')', Scorer) As CloseBrackets

    FROM @T

)

The select statement:

SELECT  CASE WHEN OpenBrackets > 0 AND CloseBrackets > OpenBrackets

        THEN

           STUFF(Scorer, OpenBrackets, CloseBrackets - OpenBrackets + 1, '') 

        ELSE

            Scorer

        END As Scorer

FROM CTE

Results:

Scorer

Ellis J.

Ellis J.

Ellis J.

Trueman J.

Brough D.

McGillvary J.

McGillvary J.

Brough D.

Ellis J.

a row with  in the middle

Just an open bracket (forgot to close 

Just a close bracket forgot to open)

no brackets at all

brackets ) in reversed order (

Below query works for you

SELECT LTRIM(RTRIM(REPLACE(Scorer, SUBSTRING(Scorer, CHARINDEX('(', Scorer), CHARINDEX(')', Scorer) - CHARINDEX('(', Scorer) + 1), '')))

FROM <TABLENAME>

These two pieces of information (the name and action) should not be in the same column. You should create a separate column for name and for action. And if the position of the action (before or after the name) is important, you might even need an additional column for that.

When you have migrated your data after that - in other words when you have cleaned up - you could still create a view or a computed column to output the scorer the way you do now, for example

ALTER TABLE my_table ADD scorer AS athlete_name + ' (' + action + ')'

You could try:

SELECT Scorer

,CASE WHEN PATINDEX('%(%)%',Scorer) > 1 

THEN LEFT(Scorer, PATINDEX('%(%)%',Scorer)-1)

ELSE RIGHT (Scorer, LEN(Scorer) - CHARINDEX(')',Scorer,1) )

END AS ColumnName

FROM ScoreTable

this should work assuming you only expect 1 instance if the pattern per row, but will work whether the "()" data is at the front or the back of the values

You can use this query

with t(str) as

(

 select 'Ellis J.(Conversion Goal)' union all

 select '(Conversion Goal)Brough D.' union all   

 select '  (Try)McGillvary J.'   

)

select (case when charindex('(', ltrim(str)) = 1 then

              substring(str,charindex(')', str)+1,len(str))

             else           

               left(str, charindex('(', str) - 1)          

             end) as "Scorers"        

  from t



    Scorers

  --------------

   Ellis J.

   Brough D.

   McGillvary J.

by contribution of substring, charindex and left functions together. ltrim is used against probabilty of spaces left before ( character at the beginning of the string.

Rextester Demo