InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 03:38:00 GMT20http://www.sqlservercentral.com/Forums/Topic1366255-8-1.aspx[quote][b]Eliza (10/7/2012)[/b][hr]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[/quote]This should work so long as the forname change appears only once in the string:[code="sql"]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 sourceCROSS APPLY (SELECT p1 = 1 + LEN('CustomerFornm: Changed From ') + CHARINDEX('CustomerFornm: Changed From',d.MyString,1)) aCROSS APPLY (SELECT p2 = CHARINDEX(' ',d.MyString,a.p1+1)) bCROSS APPLY (SELECT p1 = b.p2+1+LEN(' to ')) xCROSS APPLY (SELECT p2 = CHARINDEX(' ',d.MyString,x.p1+1)) y[/code]Sun, 07 Oct 2012 13:31:11 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1366255-8-1.aspxHi 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.ElizaSun, 07 Oct 2012 12:09:02 GMTElizahttp://www.sqlservercentral.com/Forums/Topic1366255-8-1.aspx[quote][b]ACinKC (10/2/2012)[/b][hr]Hmmm....these solutions would work if "CustomerFomm" is [i]always[/i] followed by "CustomerDOB", but I don't think we should take that as a constant (without input from the OP). Once you've found the beginning of the "CustomerFomm" segment, you'd have to parse through it to find the bits you need: Between first and second tick/quote is the old data, and between third and fourth ticks/quotes is the new data. Work through that pattern and you should be able to get your solution.[/quote]On the same line even we can not assume quote pattern in example as constant pattern ;-)Only OP can tell the requirement and pattern.Tue, 02 Oct 2012 21:57:51 GMTrhythmkhttp://www.sqlservercentral.com/Forums/Topic1366255-8-1.aspx[quote][b]Eliza (9/30/2012)[/b][hr]Hi all,I have a big long text string that can vary in lengh and contrain audit information about data that has changed in the database...ThanksEliza[/quote]Which version of SQL Server are you using?What datatype is the string? Is it a column value?Tue, 02 Oct 2012 08:40:10 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1366255-8-1.aspxHmmm....these solutions would work if "CustomerFomm" is [i]always[/i] followed by "CustomerDOB", but I don't think we should take that as a constant (without input from the OP). Once you've found the beginning of the "CustomerFomm" segment, you'd have to parse through it to find the bits you need: Between first and second tick/quote is the old data, and between third and fourth ticks/quotes is the new data. Work through that pattern and you should be able to get your solution.Tue, 02 Oct 2012 08:23:25 GMTACinKChttp://www.sqlservercentral.com/Forums/Topic1366255-8-1.aspx[quote][b]bitbucket-25253 (9/30/2012)[/b][hr]Is this what you want / need ?[code="sql"]SELECT SUBSTRING(@x,28+CHARINDEX ('CustomerFornm: Changed from ',@x),CHARINDEX('CustomerDOB',@X,1)-77) AS 'Is this really what you want?'Result:Is this really what you want?Harry to Ben [/code][/quote]Actually little correction in Ron's script.[code="sql"]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?'[/code]Mon, 01 Oct 2012 00:15:46 GMTrhythmkhttp://www.sqlservercentral.com/Forums/Topic1366255-8-1.aspxIs this what you want / need ?[code="sql"]SELECT SUBSTRING(@x,28+CHARINDEX ('CustomerFornm: Changed from ',@x),CHARINDEX('CustomerDOB',@X,1)-77) AS 'Is this really what you want?'Result:Is this really what you want?Harry to Ben [/code]Sun, 30 Sep 2012 19:41:44 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1366255-8-1.aspxHi all,I have a big long text string that can vary in lengh and contrain audit information about data that has changed in the database.An example string would beCustomerSurnm: Changed From 'Smith' to 'John-Smith' CustomerFornm: Changed From 'Harry' to 'Ben' CustomerDOB Changed from '12/12/1978' to '12/12/1979'What I need to do is pick out the forename changes in the case I want to pull out the stringCustomerFornm: Changed From 'Harry' to 'Ben'The rest of the text disregarded.The lengh of the string will vary due to the name size however because the audit trail is generated by the computer program is will always start with CustomerFornm: Changed From Any ideas on how I can achieve this please, I have played with SUBSTRING and CHARIndex. I can get the beginning bit working using . . . .SUBSTRING(ChangeInfo, (29+CHARINDEX ('CustomerFornm: Changed from ',ChangeInfo)),LEN(ChangeInfo))however this returns something likeCustomerFornm: Changed From 'Harry' to 'Ben' CustomerDOB Changed from '12/12/1978' to '12/12/1979'I can't figure out how to cut out the end part of the string to get my results.Any ideas?ThanksElizaSun, 30 Sep 2012 08:27:07 GMTEliza