Avatar of Roberto Madro R.
Roberto Madro R.
Flag for United States of America asked on

Extract parts of a string after and before delimiters

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.
DatabasesMicrosoft SQL ServerRESTSQL

Avatar of undefined
Last Comment
Raja Jegan R

8/22/2022 - Mon
arnold

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.

ASKER
Arnold, if you've written the code to do this split, please list it.  thx.
arnold

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Raja Jegan R

>>  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.

ASKER
Any real-world example ?  Thx.
Raja Jegan R

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..
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
arnold

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 R

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
arnold

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Raja Jegan R

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

ASKER
Thanks Raja  https://www.experts-exchange.com/questions/29164772/Extract-parts-of-a-string-after-and-before-delimiters.html?anchorAnswerId=42982687#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.

ASKER
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,
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
arnold

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)
arnold

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.

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
arnold

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.

ASKER
No, not all the datasets (column value) has 2 delimiters in it, it's a mixed bag, thus presenting an added challenge.
Raja Jegan R

>> 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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
arnold

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 R

>> 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.

ASKER
I'm getting;

Invalid length parameter passed to the LEFT or SUBSTRING function.

When I run the posted above script.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Raja Jegan R

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Roberto Madro R.

ASKER
Yes, it worked, many thanks Raja.
arnold

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 R

Welcome, glad to assist!!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.