Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » T-SQL » SUBSTRING And CHARINDEX to find start/end of...
Add to briefcase

SUBSTRING And CHARINDEX to find start/end of string Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Eliza
Posted Sunday, September 30, 2012 8:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 16, 2013 6:53 AM
Points: 16, Visits: 43
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.

An example string would be

CustomerSurnm: 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 string

CustomerFornm: 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 like

CustomerFornm: 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?

Thanks
Eliza
Post #1366255
bitbucket-25253
Posted Sunday, September 30, 2012 7:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:12 PM
Points: 5,103, Visits: 20,208
Is this what you want / need ?
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




If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read
Post #1366304
rhythmk
Posted Monday, October 01, 2012 12:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 10, 2012 9:30 AM
Points: 388, Visits: 603
bitbucket-25253 (9/30/2012)
Is this what you want / need ?
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




Actually little correction in Ron's script.

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?'

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1366326
ACinKC
Posted Tuesday, October 02, 2012 8:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 21, 2013 3:53 PM
Points: 114, Visits: 916
Hmmm....these solutions would work if "CustomerFomm" is always 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.
Post #1367073
ChrisM@Work
Posted Tuesday, October 02, 2012 8:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990
Eliza (9/30/2012)
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...


Thanks
Eliza



Which version of SQL Server are you using?
What datatype is the string? Is it a column value?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1367081
rhythmk
Posted Tuesday, October 02, 2012 9:57 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 10, 2012 9:30 AM
Points: 388, Visits: 603
ACinKC (10/2/2012)
Hmmm....these solutions would work if "CustomerFomm" is always 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.


On the same line even we can not assume quote pattern in example as constant pattern
Only OP can tell the requirement and pattern.

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1367386
Eliza
Posted Sunday, October 07, 2012 12:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 16, 2013 6:53 AM
Points: 16, Visits: 43
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
Post #1369551
ChrisM@Work
Posted Sunday, October 07, 2012 1:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990
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

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1369568
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.