Am stuck here and would greatly appreciate any help!
R:£30 AT:63 RT:0 D .ADD £400 @63 WK
just use charIndex and substring. The example below is assuming that there can be £ after the @ as well. Basically, splitting the string at @, for the 2nd part, i'm going from @ to ' '. the first part, reserve it, find £, then reverse it back.
declare @col varchar(500) set @col = 'R:£30 AT:63 RT:0 D .ADD £400 @63 WK' declare @p1 varchar(500),@p2 varchar(500) --split col into 2 at @ set @p1 = (reverse(substring(@col,1,CHARINDEX('@',@col)-1))) -- i will reverse here set @p2 = (substring(@col,CHARINDEX('@',@col)+1,LEN(@col))) select @p1 p1, @p2 p2 ,ltrim(rtrim(reverse(substring(@p1,1,CHARINDEX('£',@p1)-1)))) p1Final -- do the same thing as we did to p1 and reserse it --also do a trim left and right to get rid of extra spaces ,ltrim(rtrim(SUBSTRING(@p2,1,charIndex(' ',@p2)))) p2Final --this one should be self explanatory if you get the first one :)