How to find and Replace after certain character in SQL

Question

User-1256377279 posted

Hi All,

How to Find and Replace all text after certain characters in SQL.

Below is my data

2019 Audi FIS Ski Cross World Cup (Alpine Ski)
2019 Ski Cross WC (Alpine Skiing)
2019 Canada Open - FINA Artistic Swimming World Series
2019 Harry Jerome Track Classic (Athletics)
2019 Athletics Classics

Expected Data 

2019 Audi FIS Ski Cross World Cup 
2019 Ski Cross WC 
2019 Canada Open - FINA Artistic Swimming World Series
2019 Harry Jerome Track Classic 
2019 Athletics Classics

Basically i want to remove all text in Brackets which is in the end

Thanks,

Shabbir

Answer 1

User415553908 posted

-- check out documentation:
-- CHARINDEX https://docs.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql?view=sql-server-ver15
-- SUBSTRING https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver15


CREATE TABLE #test (col VARCHAR(255))
INSERT INTO #test (col) VALUES 
('2019 Audi FIS Ski Cross World Cup (Alpine Ski)'),
('2019 Ski Cross WC (Alpine Skiing)'),
('2019 Canada Open - FINA Artistic Swimming World Series'),
('2019 Harry Jerome Track Classic (Athletics)'),
('2019 Athletics Classics')
SELECT col as [raw], 
		SUBSTRING(col,  
					0, -- start at the beginning
					CASE -- second argument is length of substring we want.
						WHEN CHARINDEX('(', col) > 0 -- cater for cases where '(' is not present in the string
                                                THEN CHARINDEX('(', col)-1   -- if we find '(' - cut before it's position 
						ELSE LEN(col)				 -- otherwise get the full string length
					END
		) as trimmed 
FROM #test
DROP TABLE #test

There might be some edge cases where this simple example would not quite work (but CHARINDEX and SUBSTRING) are probably the best way to go here

Answer 2

User452040443 posted

Hi,

Shabbir, you can also try something like this:

select
    case when PATINDEX('%(%)', MyColumn) > 0
        then LEFT(MyColumn, PATINDEX('%(%)', MyColumn) - 1)
        else MyColumn
    end 
from MyTable

Hope this help

Answer 3

User77042963 posted

SELECT col
, stuff(col,isnull(nullif(charindex('(',col),0),1),charindex(')',col),'')  newCol 
FROM #test

Answer 4

User-1256377279 posted

Hi All 

I found the solution

select LEFT([Name], CHARINDEX('(', [Name]) - 1) AS Edition, Name from EventEdition
WHERE CHARINDEX('(', [Name]) > 0

Thanks Everyone