In SQL Server, how do you remove everything after a character in a string?

In this example, we will look at how to find everything after a character and then remove the content after the character.

In this example, we are going to look at our IP_List for all entries that contain a colon followed by numbers.

IP Addresses | ID

In the above list, the last two entries have identical IP addresses but differ in the colon and numbers after it.
This, in turn, will mess with the code that checks whether the IP is listed.

This example will find all records in your database that contain a [colon].

select IPAddress, IPID from IP_List where CHARINDEX(':', IPAddress) > 0

In this example, we will remove the character after the colon.

UPDATE XSS_SQL_Listed SET IPAddress = SUBSTRING(IPAddress, 1, CHARINDEX(':', IPAddress) - 1) where IPID = 11044

If you have many rows that contain substrings you need removed, you will have to remove them one by one.