third place after comma separated value in SQL

Question

i have a string and i want 3rd place word after comma.

id    String
1     India,Pakistan,China,Turkey,England
2     USA,UK,Pakistan,India
3     China,North Korea,Turkey,Iran

required result will be

id    String
1     China
2     Pakistan
3     Turkey

what you want in your answer? 3rd place word is chaina,pakistan and korea or turkey? — Hacker, Apr 19 '17 at 07:19

score 1 · Answer 1 · answered Apr 19 '17 at 07:26

You could use xml value like this

DECLARE @SampleData AS TABLE 
(
   Id int IDENTITY (1,1),
   String varchar(200)
)

INSERT INTO @SampleData
VALUES (' India,Pakistan,China,Turkey,England'), ('USA,UK,Pakistan,India'),('China,North Korea,Turkey,Iran')


;WITH temps AS 
(
   SELECT sd.Id ,CAST('<x>' + replace(sd.String, ',', '</x><x>') + '</x>' as xml) AS String
   FROM @SampleData sd
)
SELECT t.Id, t.String.value('(/x)[3]','varchar(50)') 
FROM temps t

score 0 · Accepted Answer · answered Apr 19 '17 at 07:22

Required output can be achieved using,

Syntax:

SELECT COALESCE(LTRIM(CAST(('<X>'+REPLACE(<column_name>,',' ,'</X><X>')+'</X>') AS XML).value('(/X)[3]', 'varchar(100)')), '') AS String
FROM <table_name>

Customized query for the table in Question:

SELECT id, 
    COALESCE(LTRIM(CAST(('<X>'+REPLACE(String,',' ,'</X><X>')+'</X>') AS XML).value('(/X)[3]', 'varchar(100)')), '') AS String
FROM table_1

third place after comma separated value in SQL

2 Answers2