Thanks....I was also trying other ways and also came up with this and it works too

SELECT ACC_Code = LTRIM(LTRIM(SUBSTRING(ACC_Code + '_', 0 + 1,CHARINDEX('_', ACC_Code + '_', 0 +...

I want to move this code to informix as well. Can CTEs work when you use sql fro informix ?

-- Create test Table

IF OBJECT_ID('tempdb..#Test') IS NOT NULL

DROP TABLE #Test

GO

CREATE...

Thank you guys... I now understand....

The rules for this is that loanref is distinct and should look like the result set in the excel spreadsheet

l would like an axample script.Thx in advance