Split and Get Second Row as Value

Split and get second row as value

Instead of using STRING_SPLIT you can convert your string to XML and then use .value to retrieve the 2nd element:

SELECT CAST('<t>' + REPLACE('Name1~Name2~Name3' , '~','</t><t>') + '</t>' AS XML).value('/t[2]','varchar(50)')

SQL Server - Split column data and retrieve last second value

Use a split string function, but not the built in once since it will return only the values and you will lose the location data.

You can use Jeff Moden's DelimitedSplit8K that will return the item and the item index:

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

Then you can use it to split the string and it will return a table like this:

DECLARE @string varchar(100) = '.105248.105250.104150.111004.';

SELECT *
FROM [dbo].[DelimitedSplit8K](@string, '.')

ItemNumber  Item
1   
2           105248
3           105250
4           104150
5           111004
6   

You want only the parts where there actually is an item, so add a where clause, and you want the second from last so add row_number(), and you want the entire thing in a common table expression so that you can query it:

DECLARE @string varchar(100) = '.105248.105250.104150.111004.';

WITH CTE AS
(
    SELECT Item, ROW_NUMBER() OVER(ORDER BY ItemNumber DESC) As rn
    FROM [dbo].[DelimitedSplit8K](@string, '.')
    WHERE Item <> ''
)

And the query:

SELECT Item
FROM CTE
WHERE rn = 2

Result: 104150

Split string and get Second value only

Yes:

var result = str.Split(',')[1];

OR:

var result = str.Split(',').Skip(1).FirstOrDefault();

OR (Better performance - takes only first three portions of the split):

var result = str.Split(new []{ ',' }, 3).Skip(1).FirstOrDefault();

Split Comma separated Strings & divide value of second column into separated Rows using Bigquery

Split dataframe column with second column as delimiter

Try apply.

bigdata[['title', 'location']]=bigdata.apply(func=lambda row: row['title_location'].split(row['delimiter']), axis=1, result_type="expand")

return value at a position from STRING_SPLIT in SQL Server 2016

The simple answer is, no. Microsoft so far have refused to provide Ordinal position as part of the return dataset in STRING_SPLIT. You'll need to use a different solution I'm afraid. For example Jeff Moden's DelimitedSplit8k.

(Yes, I realise this is more or less a link only answer, however, pasting Jeff's solution here would effectively be plagiarism).

If you were to use Jeff's solution, then you would be able to do something like:

SELECT *
FROM dbo.DelimitedSplit8K('a,b,c,d,e,f,g,h,i,j,k',',') DS
WHERE ItemNumber = 2;

Of course, you'd likely be passing column rather than a literal string.

How to split more than one comma separated column as a separate row in SQL using CROSS APPLY

Since - according to your description - you used CROSS APPLY and your query was successfully executed, this means you are using a SQL Server DB, not MY SQL. You can do two CROSS APPLY to get your expected result. This will produce exactly the outcome you have shown in your question:

SELECT name, phone, value courses FROM
(SELECT name, value phone, courses 
FROM tblName CROSS APPLY STRING_SPLIT(phones, ',')) x
CROSS APPLY STRING_SPLIT(courses, ',')
ORDER BY name, courses, phone;

You can verify this here: db<>fiddle

But this is very risky and you really should avoid such comma-separated contents in one column. I highly recommend to create separate columns for the different values in future.

---

Related Topics