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.
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
Last Comment
Raja Jegan R
ASKER
Arnold, if you've written the code to do this split, please list it. thx.
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.
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.
>> 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/
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/
ASKER
Any real-world example ? Thx.
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..
Or else, pls share your sample data to verify the scripts once for your data..
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
Select into #temptbale string_split ('a,b,c,d,e,f,g,h,I',',')
Select * from #temptable
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
That's why I gave some approaches to convert into columns with some additional logics
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.
...
IMHO, it might be more memorable, practice to get what each function each step does for future use.
Okay, Arnold.. Got it..
I think a detailed response from him would help us guide him straight to the point..
I think a detailed response from him would help us guide him straight to the point..
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
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
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,
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,
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>
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>
Another thought could be to use charindex()
Select charindex('nu>11BcH123>nic
select substring('nu>11BcH123>nic
1st element
Note the change
select substring('nu>11BcH123>nic
Select charindex('nu>11BcH123>nic
select substring('nu>11BcH123>nic
1st element
Note the change
select substring('nu>11BcH123>nic
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,
,SUBSTRING(F.FilmSynopsis,
LEN(SUBSTRING(F.FilmSynops
LEN(F.FilmSynopsis)) AS RightSide
FROM
dbo.tblFilm AS F
SELECT F.FilmID, F.FilmSynopsis
,SUBSTRING(F.FilmSynopsis,
,SUBSTRING(F.FilmSynopsis,
LEN(SUBSTRING(F.FilmSynops
LEN(F.FilmSynopsis)) AS RightSide
FROM
dbo.tblFilm AS F
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, (>)?
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, (>)?
ASKER
No, not all the datasets (column value) has 2 delimiters in it, it's a mixed bag, thus presenting an added challenge.
>> 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..
To match your query, youc an use this..
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
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
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?
Where do you need this info?
>> 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..
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
ASKER
I'm getting;
Invalid length parameter passed to the LEFT or SUBSTRING function.
When I run the posted above script.
Invalid length parameter passed to the LEFT or SUBSTRING function.
When I run the posted above script.
ASKER CERTIFIED SOLUTION
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.
ASKER
Yes, it worked, many thanks Raja.
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
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
Welcome, glad to assist!!
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.