Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SUBSTRING And CHARINDEX to find start/end of string Expand / Collapse
Author
Message
Posted Sunday, September 30, 2012 8:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 5, 2014 1:33 PM
Points: 33, Visits: 81
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
Posted Sunday, September 30, 2012 7:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Posted Monday, October 1, 2012 12:15 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:28 AM
Points: 579, Visits: 914
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
Posted Tuesday, October 2, 2012 8:23 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:27 PM
Points: 109, Visits: 957
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
Posted Tuesday, October 2, 2012 8:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Posted Tuesday, October 2, 2012 9:57 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:28 AM
Points: 579, Visits: 914
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
Posted Sunday, October 7, 2012 12:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 5, 2014 1:33 PM
Points: 33, Visits: 81
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
Posted Sunday, October 7, 2012 1:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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