The following script will peel off client numbers from matter numbers.

For example, if a matter has a ReferenceCode like:

ABC-0001

After the script runs, it will have a reference code of:

0001

Scenario 1: There are Client Reference Codes

In some systems, there are no Client reference codes.  This means that the Matter portion of the Matter's reference code needs to come by splitting based on a delimiter.

--In this system, the Matter's ReferenceCode is prepended with
--the Client's ReferenceCode and we don't want that.
--However, Client's themselves dont have reference codes!
--We are going to strip out everything past the first - in the matter's reference code.

DECLARE @SEPARATOR nvarchar(MAX) = '-'

UPDATE
  __M_Matters
SET
  Final_ReferenceCode = SUBSTRING(Final_ReferenceCode, 1 + LEN(@SEPARATOR) + CHARINDEX(@SEPARATOR, Final_ReferenceCode), 99999)
WHERE 1=1
  AND CharIndex(@SEPARATOR, Final_ReferenceCode) > 0

GO

Scenario 2: There are No Client ReferenceCodes

In some systems, there are Client reference codes.  This means that the Matter portion of the Matter's reference code needs to come by splitting out on a delimiter.

--In this system, the Matter's ReferenceCode is prepended with
--the Client's ReferenceCode and we don't want that.
--We are going to link matters to clients and then trim out the client reference codes.

DECLARE @SEPARATOR nvarchar(MAX) = '-'

UPDATE
  __M_Matters
SET
  Final_ReferenceCode = Right(V2.Final_ReferenceCode, len(V2.Final_ReferenceCode) - len(V1.Final_ReferenceCode) - len(@SEPARATOR) )
FROM
  __M_Contacts V1,
  __M_Matters V2
WHERE 1=1
  AND V2.Final_ClientContact_Id = V1.Id 
  AND CharIndex(V1.Final_ReferenceCode + @SEPARATOR, V2.Final_ReferenceCode) = 1

GO