Extract parts of a string after and before delimiters

Roberto Madro R.
Roberto Madro R. used Ask the Experts™
on
I've a query I'm building against a SQL Server, in the query, I have a TEXT column that I need to break apart, and the break has to happen at a certain delimiter(s), I'm using the String_Split function, and I'm able to capture the first LEFT characters that are to the left of the FIRST delimiter (which is a ">" symbol), but the rest of the string is my primary problem now, I have another delimiter in the string, and it's of the same type, a ">" symbol, so here's an example.
The string: nu>11BcH123>nic1, I'm getting the (nu) out, but I need to get the (BcH123) out and into its own column, and then get the (nic1) into its own column as well.

Your attention is very much appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
You are solutions on >
And then if you do not need the first two characters in the second element. Ltrim, or use substring to cut it.
While the third event will gave you need.
Roberto Madro R.Programmer Analyst

Author

Commented:
Arnold, if you've written the code to do this split, please list it.  thx.
Distinguished Expert 2017

Commented:
Depending on under what circumstances.
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

You can use a cursor to go through the results.
You Can assign the results to a table variable.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>>  I'm getting the (nu) out, but I need to get the (BcH123) out and into its own column, and then get the (nic1) into its own column as well.

Basically, using STRING_SPLIT function will separate delimited data to multiple rows and not columns.
Since you want to move the split strings to separate columns(not ROWS) you would need to do something in addition to STRING_SPLIT function as shown below..
Below link shows different ways to achieve it including STRING_SPLIT which would be more optimal compared to other methods, but would recommend you to try the other methods as well and test it out once.
https://datamajor.net/mssqlsplitcolumn/
Roberto Madro R.Programmer Analyst

Author

Commented:
Any real-world example ?  Thx.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Not sure whether you got a chance to go through the link I've shared above with working examples..(pls check the first option with STRING_SPLIT function)
Or else, pls share your sample data to verify the scripts once for your data..
Distinguished Expert 2017

Commented:
I find it easier to learn using individual components and build on that knowledge.

Select into #temptbale  string_split ('a,b,c,d,e,f,g,h,I',',')

Select * from #temptable
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Arnold, just wanted to clarify that asker wanted to have string values converted to columns instead of rows..
That's why I gave some approaches to convert into columns with some additional logics
Distinguished Expert 2017

Commented:
HI raja, no disagreement there. The person has a specific goal of using the contents from the string_splits

...
IMHO, it might be more memorable, practice to get what each function each step does for future use.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Okay, Arnold.. Got it..
I think a detailed response from him would help us guide him straight to the point..
Roberto Madro R.Programmer Analyst

Author

Commented:
Thanks Raja  https://www.experts-exchange.com/questions/29164772/Extract-parts-of-a-string-after-and-before-delimiters.html#a42982687
I found the reference page you posted (didn't see it a 1st) and was able to use the String Split method explained there, I'll be testing that tomorrow and will update this ticket.

Regards
Roberto Madro R.Programmer Analyst

Author

Commented:
Gents;

I've tried different scripts and I can easily get the LEFT side of the delimiter (in this case it's '>'), and the RIGHT side, but cannot get the middle part as the two delimiters are  the same '>', please provide an exact code that you've tested, if you don't mind, I'm running out of time on this one.

Many thanks.

The column value is text (data type), an example: 123456>ABCDEF>91239456,
Distinguished Expert 2017
Are you using the string_split?
Are you assigning it to a table?
If you are missing row numbers that you can reference, consider using
select row_number() over ( values) as row number, values from
string_split ('nu>11BcH123>nic1','>')


https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15


Another option is to use a cursor
That will go row by row of a table returned by string_split

Look at what you get from
select * string_split('nu>11BcH123>nic1',,>',2)
Distinguished Expert 2017
Another thought could be to use charindex()
Select charindex('nu>11BcH123>nic1','>',1)

select substring('nu>11BcH123>nic1,0,charindex('nu>11BcH123>nic1','>',1)
1st element

Note the change
select substring('nu>11BcH123>nic1',charindex('nu>11BcH123>nic1','>',1)+1,len('nu>11BcH123>nic1'))
Roberto Madro R.Programmer Analyst

Author

Commented:
This is what I have, the Right side of the delimiter and the Left side come out fine, the middle is not.

SELECT F.FilmID, F.FilmSynopsis
,SUBSTRING(F.FilmSynopsis,1, (CHARINDEX('>',F.FilmSynopsis + '>')-1)) AS LeftSide

,SUBSTRING(F.FilmSynopsis,
      LEN(SUBSTRING(F.FilmSynopsis, 1, (CHARINDEX('>',F.FilmSynopsis)+2))),
      LEN(F.FilmSynopsis)) AS RightSide
FROM
dbo.tblFilm AS F
Distinguished Expert 2017
You can
nu>11BcH123>nic1

3,
If you start searching from position of the first > +1 and instead of the entire length, but to the position of the second >
You will get the middle.

Are all datasets have only two delimeters, (>)?
Roberto Madro R.Programmer Analyst

Author

Commented:
No, not all the datasets (column value) has 2 delimiters in it, it's a mixed bag, thus presenting an added challenge.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
>> The column value is text (data type), an example: 123456>ABCDEF>91239456,

If your sample data will always be in the above format, then you can use the below script..
declare @a varchar(100) = '123456>ABCDEF>91239456'

SELECT @a, LEFT(@a, charindex('>', @a)-1) LeftSide, REVERSE(LEFT(REVERSE(@a), charindex('>', REVERSE(@a))-1)) RightSide
, SUBSTRING(@a, charindex('>', @a)+ 1, charindex('>', @a, charindex('>', @a)+1) - charindex('>', @a)-1) Middle

Open in new window


To match your query, youc an use this..
SELECT F.FilmID, F.FilmSynopsis
, LEFT(F.FilmSynopsis, charindex('>', F.FilmSynopsis)-1) LeftSide, REVERSE(LEFT(REVERSE(F.FilmSynopsis), charindex('>', REVERSE(F.FilmSynopsis))-1)) RightSide
, SUBSTRING(F.FilmSynopsis, charindex('>', F.FilmSynopsis)+ 1, charindex('>', F.FilmSynopsis, charindex('>', F.FilmSynopsis)+1) - charindex('>', F.FilmSynopsis)-1) Middle
FROM
dbo.tblFilm AS F 

Open in new window

Distinguished Expert 2017
This is why my siggestion to have the data built into either a temp table with the addition of row_count or through the use of a cursor that will go row by row.

Where do you need this info?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
>> No, not all the datasets (column value) has 2 delimiters in it, it's a mixed bag, thus presenting an added challenge.

Then you can try this script..
--declare @a varchar(100) = '123456>ABCDEF>91239456'
declare @a varchar(100) = '123456>ABCDEF'

SELECT @a, LEFT(@a, charindex('>', @a)-1) LeftSide, REVERSE(LEFT(REVERSE(@a), charindex('>', REVERSE(@a))-1)) RightSide
, CASE WHEN LEN(@a) - LEN(REPLACE(@a, '>', '')) = 2
THEN SUBSTRING(@a, charindex('>', @a)+ 1, charindex('>', @a, charindex('>', @a)+1) - charindex('>', @a)-1) 
ELSE NULL
END Middle

Open in new window

Roberto Madro R.Programmer Analyst

Author

Commented:
I'm getting;

Invalid length parameter passed to the LEFT or SUBSTRING function.

When I run the posted above script.
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Script I've shared above is working fine..
Kindly check the modified query for your logic as well..
SELECT F.FilmID, F.FilmSynopsis
, LEFT(F.FilmSynopsis, charindex('>', F.FilmSynopsis)-1) LeftSide, REVERSE(LEFT(REVERSE(F.FilmSynopsis), charindex('>', REVERSE(F.FilmSynopsis))-1)) RightSide
, SUBSTRING(F.FilmSynopsis, charindex('>', F.FilmSynopsis)+ 1, charindex('>', F.FilmSynopsis, charindex('>', F.FilmSynopsis)+1) - charindex('>', F.FilmSynopsis)-1) Middle
, CASE WHEN LEN(F.FilmSynopsis) - LEN(REPLACE(F.FilmSynopsis, '>', '')) = 2 THEN SUBSTRING(F.FilmSynopsis, charindex('>', F.FilmSynopsis)+ 1, charindex('>', F.FilmSynopsis, charindex('>', F.FilmSynopsis)+1) - charindex('>', F.FilmSynopsis)-1) ELSE NULL END Middle
FROM dbo.tblFilm AS F 

Open in new window

Roberto Madro R.Programmer Analyst

Author

Commented:
Yes, it worked, many thanks Raja.
Distinguished Expert 2017
A cursor is useful in many other situations.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-ver15
Another useful tool is to use CTE which is similar to the temptable, but it creates a table based on your queries

https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Welcome, glad to assist!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial