SUBSTRING And CHARINDEX to find start/end of string

Question

SUBSTRING And CHARINDEX to find start/end of string

Eliza

SSC Eights!

Points: 967
More actions
September 30, 2012 at 8:27 am

#104174

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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[/url]
Before posting a performance problem please read[/url]

rhythmk SSCertifiable Points: 7271 More actions · Answer 2

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

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

ACinAZ SSCommitted Points: 1849 More actions · Answer 3

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 SSC Guru Points: 186127 More actions · Answer 4

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

rhythmk SSCertifiable Points: 7271 More actions · Answer 5

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

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Eliza SSC Eights! Points: 967 More actions · Answer 6

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 SSC Guru Points: 186127 More actions · Answer 7

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