split field into columns

I have a field returned to reporting service that looks like:
name: some text ||| address: some text
I would like to be able to split the field (on |||) and show it as 2 separate columns in SSRS
I also want to lose the "name:" and "address:" portion of the strings.
LVL 29
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris LuttrellSenior Database ArchitectCommented:
The best way to do this is probably to add a calculated column to the dataset and use an expression to split the field.  I can describe how to do calculated columns and/or the expression to split that if you need me to or you may already know all that since you are Intermediate in this subject.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QPRAuthor Commented:
thanks, I solved this at the report level using a combo on mid, instr and replace.
Kevin CrossChief Technology OfficerCommented:
If you prefer to do this on the T-SQL side, just use the REPLACE, LEFT, RIGHT, REVERSE, CHARINDEX functions together like so:
SET @text = 'name: some kind of name ||| address: some kind of address'
SELECT LTRIM(RTRIM(REPLACE(LEFT(@text, CHARINDEX('|||', @text)-1), 'name:', ''))) AS [name]
, LTRIM(RTRIM(REPLACE(RIGHT(@text, CHARINDEX('|||', REVERSE(@text))-1), 'address:', ''))) AS [address]

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Chris LuttrellSenior Database ArchitectCommented:
I somewhat object in that there is not anything wrong with the solution I provided and it does what the requestor asked.  It is not like there was any feedback saying something did not work, or needed more guidance.
QPRAuthor Commented:
Heard you the first time :)
Now that I re-read (was in a major rush yesterday) I see that what you posted was what I ended up doing. Even though I had done it by the time I came to check replies. None-the-less the points are yours.
Chris LuttrellSenior Database ArchitectCommented:
I appologize, my browser went crazy and did not seem to be accepting me hitting the OK button, then posted them all at once.  I requested help to get them removed but have not got a reponse back from moderators yet.
QPRAuthor Commented:
looks like they've done it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.