How to retreive one single value in comma separated columns form, from a table in sql server?

I have a column with values like the one below. I need to separate value number 10 from each row in SQL sever (being value 1 the first one in the string, second after the first comma and so on).

Here is my column:

Row 1    N,l,S,T,A,,<all>,,N,A,N,N,N,Y,Y,,Y,Y,Y,,AA,SA
Row 2    M,,A,S,AS,SS,AS,N,N,N,N,Y,Y,Y,ENTERPRISE,S,,A
Row 3    L,,A,D,S,A,A,AA,Y,Y,Y,YNN,N,N,N,N,A,AA,AD,D,D

I need the values in place 10 to use in a table (I can discard the rest of the values in the string). How do I get only that one value in a column? The table I have has more than 1,000 rows so this is just an example, I need a code that can take the value of each position in the row as the source of the string.

Here is the result:

Row 1    A
Row 2    N
Row 3    Y

Answer

You could clean your string first for any special chars, such as the occurrence of your all value contained in

< >.

After cleaning the string you should have each value consistenly delimeted that can then be presented to an XML function, this function will isolate values from a delimeted string based on its numeric position from left to right.

DECLARE @TEST TABLE (STRING NVARCHAR(150));
INSERT INTO @TEST
VALUES 
('N,l,S,T,A,,<all>,,N,A,N,N,N,Y,Y,,Y,Y,Y,,AA,SA'),
('M,,A,S,AS,SS,AS,N,N,N,N,Y,Y,Y,ENTERPRISE,S,,A'),
('L,,A,D,S,A,A,AA,Y,Y,Y,YNN,N,N,N,N,A,AA,AD,D,D')

SELECT 
CONVERT(XML,'<x>'+REPLACE(REPLACE(REPLACE(STRING,'>',''),'<',''),',','</x><x>')+'</x>').value('/x[10]','NVARCHAR(150)') [VALUE 10],
STRING
FROM @TEST

This does rely on a clean string being passed to the function, if the string is not clean of special chars or consistently delimeted then the function will fail.

VALUE 10    STRING
A           N,l,S,T,A,,<all>,,N,A,N,N,N,Y,Y,,Y,Y,Y,,AA,SA
N           M,,A,S,AS,SS,AS,N,N,N,N,Y,Y,Y,ENTERPRISE,S,,A
Y           L,,A,D,S,A,A,AA,Y,Y,Y,YNN,N,N,N,N,A,AA,AD,D,D

For Clarity, you would use this function like so:

SELECT
  CONVERT(XML,'<x>'+REPLACE(REPLACE(REPLACE(STRING,'>',''),'<',''),',','</x><x>')+'</x>').value('/x[10]','NVARCHAR(150)') [VALUE 10]
FROM YOURTABLE

Simply replace STRING in the function with your column name, and that should do it.