I have a column in the table with varchar(2000)
multiple values stored into the same column.
Upper Right, Clinical Info for Radiologist: Bone Tumor; Additional Info to DI Tech: xxx please check
I would like to get only the value for Additional Info to DI Tech:
and to separate this as a column
Have a Google for "sql csv splitter jeff moden".
Its a real shame whoever built the database decided to store multiple values in a single column in that way (rather than in a child-table) - is there any guarantee that "Additional Info to DI Tech:" is always stored in exactly that way, no additional spaces, no variation to spelling, and every item in the column neatly separately, on every row without exception, using ";" ?
Unfortunately this is a vendor controlled shop and we have limited access to tables, I am sure there must be child table where the information is stored, but cant access that.
Just struggling to do this ..
Did you look at the Splitter ?
I did use it, and here is the result and the code.
SELECT SUBSTRING(Summaryline, 1, CASE CHARINDEX('/', Summaryline) WHEN 0 THEN LEN(Summaryline) ELSE CHARINDEX('/', Summaryline)-1 END) AS Label,
SUBSTRING(Summaryline, CASE CHARINDEX('/', Summaryline) WHEN 0 THEN LEN(Summaryline)+1 ELSE CHARINDEX('/', Summaryline)+1 END, 2000) AS Value
FROM CV3Order
WHERE SummaryLine LIKE '%Additional Info to DI Tech:%'
select substring(Summaryline, 1, charindex(',',Summaryline) - 1) [Extremity Quadrant],
I want to remove the ',Header value' from both 2 and 3 columns,
looks like I missed something, can anyone help?
substring(Summaryline,charindex(',',Summaryline),len(Summaryline) - charindex(';',Summaryline)) [Clinical Info for Radiologist],
substring(Summaryline, charindex(';',Summaryline) +1,len(Summaryline) - charindex(';',Summaryline)) [Additional Info to DI Tech]
from CV3Order
WHERE summaryline LIKE '%Additional Info to DI Tech:%'
and name='ct extremity'
order by CreatedWhen desc
If it is always in the same place, and a fixed (or easily determinable) length then you could use STUFF() - that will replace a section of a string, from a given start point / length, with a new string (which can be blank).
If the location within the string is variable then probably easier to use REPLACE()
SELECT Name [Order Item Name],
CreatedBy,
CreatedWhen,
substring(Summaryline, 1, charindex(',',Summaryline) - 1) [Extremity Quadrant],
substring(Summaryline, charindex(':',Summaryline) +1, len(Summaryline) - charindex(';',Summaryline)) [Clinical Info for Radiologist],
substring(Summaryline, charindex(';',Summaryline) +30, 255) [Additional Info to DI Tech]
The first and the third column is ok, The second column . . is not getting correct data, for example the third row second column I do not need any values after 'Arthritis'
Picture isn't any use as I can't test your data. Better would be to post a CREATE TABLE statement and some INSERTS to populate it with sample data, and then folk here can provide solutions for your data.
How can I use replace or stuff ?
Post a sample table and some example data INSERTs and folk here will make you a worked example.
The DOCs are on the MS website:
Here is the sample data,
The following data is in one column called Summaryline
I need to split this to 3
I need to split this to 3 columns and the output should be like this
Heading 1 is Value before the Clinical Info for Radiologist: (separated by ,)
Heading 2 is Value after the Clinical Info for Radiologist: and before Additional info to DI Tech: (separated by colon)
Heading 3 is Value after Additional info to DI Tech: (separated by colon)
As @Kristen had indicated earlier, a screenshot is really of very little use to someone who wants to write a solution for you. Instead, what would be useful is to provide consumable data as shown below. I have just put place holders for the data because it is hard to read your screenshot and type that in. You on the hand have that data ready that can be copied and pasted.
-- sample input
create table #SampleInput(s varchar(1024));
insert into #SampleInput values
('Left, Clinical info for.....'),
('Left, Clinical info for.....'),
('Left, Clinical info for.....');
-- sample output
create table #SampleOutput(
Heading1 varchar(256),
Heading2 varchar(256),
Heading3 varchar(256)
);
insert into #SampleOutput values
('Left', 'For Chemo...', 'Tunnelled..'),
('Right', 'For Stem cell...', 'For Donon -..'),
('Left', 'SAOTPD...', 'Proceed only..'),
('Right', 'Arthritis..', 'Testing..')removing