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.

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.

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

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.

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,

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

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

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.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roberto Madro R.

ASKER