SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SUBSTRING And CHARINDEX to find start/end of string


SUBSTRING And CHARINDEX to find start/end of string

Author
Message
Eliza
Eliza
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7909 Visits: 25280
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
rhythmk
rhythmk
SSC Eights!
SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)

Group: General Forum Members
Points: 918 Visits: 1078
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/
:-)
ACinAZ
ACinAZ
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 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.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16484 Visits: 19557
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
rhythmk
rhythmk
SSC Eights!
SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)

Group: General Forum Members
Points: 918 Visits: 1078
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/
:-)
Eliza
Eliza
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16484 Visits: 19557
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search