Ussing Substring, Patindex, Charindex to extract data from a string

Question

Ussing Substring, Patindex, Charindex to extract data from a string

James Millar-305032

Ten Centuries

Points: 1047
More actions
June 15, 2011 at 11:55 am

#242191

Hi guys,
I have a conundrum that I would really appreciate some help with...
Basically I need to extract data from the string that could contain data structured like so
CA1-AGENT/CA2-AGENTHO/CA3-CALC1/CA4-CALC2/CA5-NONE
or
CA1-CALC1/CA2-AGENT/CA3-CALC2/CA5-NONE
The above are just 2 combinations/examples. I am looking to extract the CA1, CA2, CA3 etc data into corresponding fields in a view. What I am looking for is data after the "CAx-" and upto the "/CAx", so basically the data in between the - and the /.
It is not safe to assume that a "CA1-" has a "/CA2", it could be that there is no "/CA2" but there is a "/CA3" or "/CA4" instead.
This is what I have so far, but my problem appears to be when there is no corresponding ending "/CAx" data I dont know how to search for more than one "/CAx" if the next logical "/CAx" does not exist in the string.
CASE WHEN AccountCode_FD LIKE '%CA%' THEN
--the AccountCode_FD could be blank or even contain data that has no '%CA%' string
SUBSTRING( AccountCode_FD, CHARINDEX( 'CA2-', AccountCode_FD ) + 4 , ( CHARINDEX( '/CA3-', AccountCode_FD ) - CHARINDEX( 'CA2-', AccountCode_FD )) - 4 )
ELSE
''
END
Hope this makes sense - please let me know if you have any questions.
Many thanks in advance
-James

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

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

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

Remember we can't see over your shoulder and have no intimate knowledge of your project. Please help us help you by providing ddl, sample data (insert statements), desired output (based on your sample data) and what you have tried so far. You will get tested and fast results. Please see the link in my signature about best practices for posting questions to increase the success rate of getting a solution that works.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 2

Here is some more info on what I would expect to see in the relevant output data:

I have to extract data into separate fields for the CA1, CA2, CA3... up to CA7 values embedded in the string.

If my string contained the following data this is what I would expect to see in the relevant fields:

CA1-AGENT/CA2-AGENTHO/CA4-CALC2/CA5-224455/CA7-911911/

CA1 = AGENT

CA2 = AGENTHO

CA3 = '' -- there is no value for CA3 so this would be empty

CA4 = CALC2

CA5 = 224455

CA6 = '' -- there is no value for CA6 so this would be empty

CA7 = 911911

Basically I am after the data in between the "-" and the "/" for every CAx instance.

The above string is just an example, I could have a string that contains data for CA1 right through to CA7, or I could have a string that only contains data for CA2 and CA3 - the combinations are endless so to speak.

Hope this helps clarify what I am trying to achieve here.

Thanks,

James

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

Without something that makes easy for me or others to readily start working on a solution you are not likely going to find much help. I was asking for a few things from you to help us help you. Namely some ddl, sample data and desired output. What have you tried etc.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

Have you considered replacing the - and / with say a comma, using the REPLACE function, then splitting the string passed on commas? That can then be broken up using say Jeff Moden's split string routine, which you can find here on SSC at

http://qa.sqlservercentral.com/articles/Tally+Table/72993/

and be sure to read all the comments posted, on the article and you will find multiple other examples of T-SQL code to split various strings into various resulting formats

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]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

James Millar-305032 (6/15/2011)
Here is some more info on what I would expect to see in the relevant output data:
I have to extract data into separate fields for the CA1, CA2, CA3... up to CA7 values embedded in the string.
If my string contained the following data this is what I would expect to see in the relevant fields:
CA1-AGENT/CA2-AGENTHO/CA4-CALC2/CA5-224455/CA7-911911/
CA1 = AGENT
CA2 = AGENTHO
CA3 = '' -- there is no value for CA3 so this would be empty
CA4 = CALC2
CA5 = 224455
CA6 = '' -- there is no value for CA6 so this would be empty
CA7 = 911911
Basically I am after the data in between the "-" and the "/" for every CAx instance.
The above string is just an example, I could have a string that contains data for CA1 right through to CA7, or I could have a string that only contains data for CA2 and CA3 - the combinations are endless so to speak.
Hope this helps clarify what I am trying to achieve here.
Thanks,
James

It's a "double_split"... otherwise known as a "2 dimensional array". Please see the following article for one way to handle such things...

http://qa.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 6

Thanks Jeff and Ron for your input - I am a bit of a T-SQL novice but I will take a look and try to relate this to my conundrum!

Thanks again - James

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

C'mon back if the article doesn't do it for you. I coud write the code for you but I'd just be copying it from my own article and then you'd miss all the reasons why it all works. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 8

Ah Jeff you are just too good to be true 😛

I did manage to create a working substring in the end:

CASE WHEN CHARINDEX( '/', AccountCode_FD, CHARINDEX( 'CA1-', AccountCode_FD )) > 0 THEN SUBSTRING( AccountCode_FD,

CHARINDEX('CA1-', AccountCode_FD ) + 4, -- this is the start point

CHARINDEX( '/', AccountCode_FD, CHARINDEX( 'CA1-', AccountCode_FD )) - ( CHARINDEX( 'CA1-', AccountCode_FD ) +4 ) -- this is the length

)

ELSE

''

END AS findCA1,

I repeat this for CA2, CA3 etc...