December 3, 2015 at 2:25 pm
I have a field 'StatusCode' and the data in that field is 'PENDING: NEW (direct or HUB referral received)'....I want to split the data up into 2 fields. In theory I would like:
A field called Status that shows 'Pending'
And a field called SubStatus that shows 'NEW (direct or HUB referral received)'
Is this possible?
December 3, 2015 at 2:47 pm
cory.bullard76 (12/3/2015)
I have a field 'StatusCode' and the data in that field is 'PENDING: NEW (direct or HUB referral received)'....I want to split the data up into 2 fields. In theory I would like:A field called Status that shows 'Pending'
And a field called SubStatus that shows 'NEW (direct or HUB referral received)'
Is this possible?
Absolutely. Split on the colon and you'll have your two values. You have to ask yourself, though, about what happens where there is no colon or when there are two colons in the value. If the first one is the only one that matters, then you're in business. If not, you'll have to decide how to handle it. Here's one example of parsing the string, but there are many more.
WITH cteData AS (
SELECT 'PENDING: NEW (direct or HUB referral received)' column_name
)
SELECT FirstPart = SUBSTRING(column_name, 1, CHARINDEX(':', column_name) - 1),
SecondPart = SUBSTRING(column_name, CHARINDEX(':', column_name) + 1, 999)
FROM cteData;
December 3, 2015 at 4:18 pm
Thanks! Do I need to set it up as a CTE? And it will be only one colon
December 4, 2015 at 5:14 am
No, the CTE was just a demonstration of how to hit a column. You can run it against a table by replacing the CTE with your table. Example:
SELECT FirstPart = SUBSTRING(s.SomeColumn, 1, CHARINDEX(':', s.SomeColumn) - 1),
SecondPart = SUBSTRING(s.SomeColumn, CHARINDEX(':', s.SomeColumn) + 1, 999)
FROM dbo.SomeTable s;
December 4, 2015 at 7:39 am
worked perfectly....thanks Ed!
December 4, 2015 at 8:01 am
Glad to be of help. Thanks for the feedback.
December 7, 2015 at 2:07 am
Make a choice a service that contributes the chance to truly know your skill and achievements to turn out a professional resume that speaks to you effectively. You additionally need a service where each resume is not done in a treat reaper blueprint so that each one of them sounds and has a striking resemblance when it achieves a potential employer. That's privilege - resumes. Get the gatherings you require with resume and initial letter writing help from the professional resume writing service.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply