Sql Server replace, remove all after certain character

sql

sql-queries

string-manipulation

database-optimization

byAlex Kataev·Mar 7, 2025

Quickly trim everything after a specific character with SQL Server using LEFT and CHARINDEX:

SELECT LEFT(Column, CHARINDEX('Char', Column) - 1)
FROM Table
WHERE CHARINDEX('Char', Column) > 0;

Here, replace Column with your column name, 'Char' with your delimiter, and Table with your table name. This chunk of code drops every character after 'Char', returning the substring up to it. When 'Char' isn't present, the original content remains untouched.

Updating records

To update a column effectively and delete everything after a certain character:

UPDATE Table
SET Column = LEFT(Column, NULLIF(CHARINDEX('Char', Column + 'Char') - 1, -1))
WHERE CHARINDEX('Char', Column) > 0;

This code considers all scenarios—rows without 'Char' aren't updated, and null or empty strings won't break your SQL statement.

Special Scenarios

Take note of possible edge cases when your data integrity matters:

If no semi-colon, skip the update to reduce unnecessary writes.
Append a semi-colon to ensure CHARINDEX finds a position.
Use NULLIF to gracefully handle situations when 'Char' doesn't exist.

Optimization Tactics

Efficiency and accuracy should be your top priorities when manipulating huge datasets. The above approach modifies records selectively, avoiding performance drain from updating rows that don't need any changes.

Manipulation Techniques

Handling nulls and emptiness

Regarding robustness against NULL or empty columns:

WHERE CHARINDEX('Char', ISNULL(Column + 'Char', '')) > 0

The Power of Substring

If SUBSTRING is more to your taste:

UPDATE Table
SET Column = SUBSTRING(Column, 1, CHARINDEX('Char', Column + 'Char') - 1)
WHERE CHARINDEX('Char', Column) > 0;

Time for Testing

Follow through test data with expected results:

Text with 'Char.
Text without 'Char.
NULL or empty records.

Avert the Unpredictable

Safeguarding Against Errors

Use transaction control and error handling for safety:

BEGIN TRY
   BEGIN TRANSACTION
   -- Update statement here
   -- That's some serious game you're playing here!
   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION
   -- Oops! Something went wrong, time to step back 🙃.
END CATCH