Eliza (10/7/2012)
Hi All,Thanks for some of the solutions, I've tried a few.
SELECT SUBSTRING(@var,28+CHARINDEX ('CustomerFornm: Changed from ',@var),CHARINDEX('CustomerDOB',@var)- CHARINDEX ('CustomerFornm: Changed from ',@var)-28) AS 'Is this really what you
want?'
Doesn't work as it requires me to feed in a variable, @var. My SQL has to be in a view. I don't have a choice on the matter due to how the 3rd party software works. CustomerDOB will not always follow, it may not even exist and there are a whole load of other 'Changed From' categories that can be present. For example 'NI: Changed from '
Essentially it is variable all the time.
The datatype is varchar and we have SQL server 2008, applologies as this may be a SQL server 2000 forum I had not realised.
I have created a function that loops through each character in the string but this seems a very inefficient way of trying to achieve my result.
Thanks for the help and replies so far.
Eliza
This should work so long as the forname change appears only once in the string:
SELECT d.MyString, a.p1, b.p2, x.p1, y.p2,
OldForename = SUBSTRING(d.MyString,a.p1,b.p2-a.p1),
NewForename = SUBSTRING(d.MyString,x.p1,y.p2-x.p1)
FROM (
SELECT MyString = 'CustomerSurnm: Changed From ''Smith'' to ''John-Smith'' CustomerFornm: Changed From ''Harry'' to ''Ben'' CustomerDOB Changed from ''12/12/1978'' to ''12/12/1979'''
) d -- sample table source
CROSS APPLY (SELECT p1 = 1 + LEN('CustomerFornm: Changed From ') + CHARINDEX('CustomerFornm: Changed From',d.MyString,1)) a
CROSS APPLY (SELECT p2 = CHARINDEX(' ',d.MyString,a.p1+1)) b
CROSS APPLY (SELECT p1 = b.p2+1+LEN(' to ')) x
CROSS APPLY (SELECT p2 = CHARINDEX(' ',d.MyString,x.p1+1)) y
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden