How to parse this, 'F0-B20170225131636437{A^CM.INIT}'

Question

How to parse this, 'F0-B20170225131636437{A^CM.INIT}'

NineIron

SSChampion

Points: 12529
More actions
March 7, 2017 at 12:22 pm

#321067

What is the best way to parse out 'CM.INIT' from 'F0-B20170225131636437{A^CM.INIT}'?

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

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

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

NineIron - Tuesday, March 7, 2017 12:22 PM
What is the best way to parse out 'CM.INIT' from 'F0-B20170225131636437{A^CM.INIT}'?

Can you explain the rules?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323482 More actions · Answer 2

if we assume there's only one carat, and only one curly brace:
/* (No column name) CM.INIT CM.INIT */WITH MySampleData(val) AS ( SELECT 'F0-B20170225131636437{A^CM.INIT}' UNION ALL SELECT 'F1-B30170226136437{A^CM.INIT}' ) --assuming only one carat exists, and only one right curly brace SELECT CASE WHEN CHARINDEX('^',val) > 0 AND CHARINDEX('}',val) > CHARINDEX('^',val) THEN SUBSTRING(val,CHARINDEX('^',val) + 1,CHARINDEX('}',val) - (CHARINDEX('^',val) + 1)) ELSE '' END FROM MySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sgmunson SSC Guru Points: 110588 More actions · Answer 3

NineIron - Tuesday, March 7, 2017 12:22 PM
What is the best way to parse out 'CM.INIT' from 'F0-B20170225131636437{A^CM.INIT}'?

Like Luis said, what are the rules? Does the field that contains this value have any variations in length or format? What other possible values can be present? Are the braces "{ }" ALWAYS present in any given value? Is the ^ present in every record? There needs to be an exact set of rules one can follow to determine if that value can consistently be retrieved from a given record. And you have to be TOTALLY anal about ensuring that all the data ALWAYS follows ALL the rules. There are going to be occasions when data suddenly no longer follows the rules. Sometimes, you can change the rules to accommodate the new variation. Other times, there needs to be a whole new set of rules. In some cases, data just plain breaks the rules in such a way that a new rule or a change to the rules just isn't possible. Let us know...

Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)

NineIron SSChampion Points: 12529 More actions · Answer 4

I didn't think you needed rules but, here goes.............
The ^ is always the character before the string and the } is always after the string.

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

NineIron - Wednesday, March 8, 2017 4:08 AM
I didn't think you needed rules but, here goes.............
The ^ is always the character before the string and the } is always after the string.

That's part of what they wanted to make sure of. The other thing they want to make sure of is, will EVERY row ALWAYS have those two things in it... ALWAYS?

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

NineIron SSChampion Points: 12529 More actions · Answer 6

Got it. I would have said so otherwise in my first post but, I understand why they would want those specifics.

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

Did you try Lowell's code (looks like it will work and he's really good about testing his stuff) and, if so, are you all set now?

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

NineIron SSChampion Points: 12529 More actions · Answer 8

Sorry, yes I did. I get very easily distracted here at work.
Thanx Lowell.