REPLACE (T-SQL)[
^] or
SUBSTRING[
^] together with
PATINDEX[
^] is what you're looking for.
The main idea is:
SELECT REPLACE(FieldName, SUBSTRING(FieldName, PStart, LEN(FieldName)-PEnd),'') AS NewFieldValues
FROM (
SELECT FieldName, PATINDEX('%(City:%',FieldName) AS PStart, PATINDEX('%)%',FieldName) AS PEnd
FROM TableName
WHERE PATINDEX('%(City:%',FieldName)>0
) AS T
You did not provide enough information to help you direct.
'Working' sample:
DECLARE @tmp TABLE (SomeString VARCHAR(255))
INSERT INTO @tmp (SomeString)
VALUES('Address : Nehru Road Ext., Vile Parle (East)
Near Domestic Airport, Mumbai – 400099.
Maharashtra. INDIA. ( City :- Mumbai )
Tel : Not Available'),
('Address : Bahru Road Ext., Vile Parle (West)
Near Domestic Airport, Calcuta – 422099.
Maharashtra. INDIA. ( City :- Calcuta )
Tel : UN')
SELECT REPLACE(SomeString, SUBSTRING(SomeString, PStart, PEnd-1-Pstart),'') AS NewFieldValues
FROM (
SELECT SomeString, PATINDEX('%( City%',SomeString) AS PStart, PATINDEX('%Tel%',SomeString) AS PEnd
FROM @tmp
WHERE PATINDEX('%( City%',SomeString)>0
) AS T
Result:
Address : Nehru Road Ext., Vile Parle (East)
Near Domestic Airport, Mumbai – 400099.
Maharashtra. INDIA.
Tel : Not Available
Address : Bahru Road Ext., Vile Parle (West)
Near Domestic Airport, Calcuta – 422099.
Maharashtra. INDIA.
Tel : UN