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?
Chris LuttrellConnect With a Mentor Senior 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.
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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.