Substring for column

Question

Substring for column

jollyegeorge

SSC Enthusiast

Points: 191
More actions
May 6, 2016 at 11:26 am

#313831

I would like to remove value after Arthritis;
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]
Extremity QuadrantClinical Info for Radiologist Additional Info to DI Tech
Upper Left Arthritis; Additional Info to DI Tech:TES TEST for Query

Viewing 14 posts - 1 through 13 (of 13 total)

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

Y.B. SSChampion Points: 11649 More actions · Answer 1

Just Arthritis? After the semicolon perhaps? This is easy to do but I don't want to give you a bad solution. Please provide sample data and how you expect it to look like.

This looks like free text...

Extremity Quadrant Clinical Info for Radiologist Additional Info to DI Tech

Upper Left Arthritis; Additional Info to DI Tech:TES TEST for Query

Trying to parse that out accurately and consistently may be an issue unless you have a guaranteed text/symbol that you can use.

SELECT quote FROM brain WHERE original = 1
0 rows returned

jollyegeorge SSC Enthusiast Points: 191 More actions · Answer 2

jollyegeorge

SSC Enthusiast

Points: 191

May 6, 2016 at 11:41 am

#1877225

Yes, after the semi column . .

Y.B. SSChampion Points: 11649 More actions · Answer 3

Here is a simple example on how to grab ALL text before the semicolon. Just keep in mind this implies that there will always be a semicolon to use as a separator.

DECLARE @myTable TABLE (strings VARCHAR(100))

INSERT INTO @myTable

VALUES ('blah, blah blah; more stuff'), ('some text before; some text after'), ('yet another example; with a semicolon')

SELECT

SUBSTRING(strings, 1, CHARINDEX(';', strings) -1)

strings

FROM

@myTable

SELECT quote FROM brain WHERE original = 1
0 rows returned

jollyegeorge SSC Enthusiast Points: 191 More actions · Answer 4

The column Summaryline has the value like this varchar(2000)

Upper Right, Clinical Info for Radiologist: Bone Tumor; Additional Info to DI Tech: xxx please check

I need to get this to 3 different columns

Upper Right (c0lumn1) Bone Tumor (column2) xxxplease check (column 3)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

If you need to split a single column into three different ones based on a 1-character delimiter, your best bet is probably DelimitedSplit8K function.

The second link in my signature has an article with the function and details about how it works. It's well worth the read.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

jollyegeorge SSC Enthusiast Points: 191 More actions · Answer 6

It wont work for me as my shop does not support . . 🙁

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

jollyegeorge (5/6/2016)
It wont work for me as my shop does not support . . 🙁

I don't get what you mean. Your shop does not support what?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

does every summaryline include the text that is in bold below?

Upper Right, Clinical Info for Radiologist: Bone Tumor; Additional Info to DI Tech: xxx please check

if so...maybe think about doing a replace with NULL before using delimitted splitter

I think it maybe best if you analyse ALL variations of the summary line to determine what is really required.

Are you able to get to the source data that create the "summaryline"....it would probably be easier to do what you want from there

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

jollyegeorge SSC Enthusiast Points: 191 More actions · Answer 9

I get error for 'Invalid object name 'dbo.DelimitedSplit8K'

Yes it does, all the summary line has these bold values.

Y.B. SSChampion Points: 11649 More actions · Answer 10

jollyegeorge (5/6/2016)
I get error for 'Invalid object name 'dbo.DelimitedSplit8K'
Yes it does, all the summary line has these bold values.

It's a custom function that you have to create on your system before you can use it.

SELECT quote FROM brain WHERE original = 1
0 rows returned

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

as Ed tried to tell you...

http://www.sqlservercentral.com/articles/Tally+Table/72993/

code is at bottom of this article.....strongly suggest you read the article and the associated discussion thread

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Alan Burstein SSC Guru Points: 61141 More actions · Answer 12

jollyegeorge (5/6/2016)
I get error for 'Invalid object name 'dbo.DelimitedSplit8K'
Yes it does, all the summary line has these bold values.

DelimitedSplit8K is not built in, you need to create it. For the code to create DelimitedSplit8K, see the link in my signature for a string splitter (or Ed's) as we both have links to the same article.

Edit: apologies for repeating what everyone already said, I did not realize there was a page 2 in this thread.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Ed Wagner SSC Guru Points: 287026 More actions · Answer 13

OP, there's a lot of good stuff in that article. I know it isn't short and can, if you aren't used to it, twist the way you think, but it's well worth the time to read it. It can really change your expectations of performance.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions