Fetch element at particular position

Question

Hi I want to fetch nth element in a statement using sql query.

Example for 2nd element:

I like fruits.

Output: like

Example for 3nd element:

I like fruits.

output: fruits.

Answer 1

You use a string-splitter which permits you to specify the delimiter and which returns the list position. For instance:

SELECT str
FROM   list_to_table(@string, ' ')
WHERE  listpos = 3

See this article on my web site for example of such functions:
http://www.sommarskog.se/arrays-in-sql.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Hi cherry11111,

Quote code from this case: how-to-select-the-nth-word-in-a-string

CREATE FUNCTION dbo.SplitWithOrder
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))


Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END


declare @string nvarchar(264)
set @string = 'I LOVE FRUIT'
SELECT * FROM dbo.SplitWithOrder(@string,' ') where ID = 3

BR,

Mia

If the response helped, do "Accept Answer" and upvote it.--Mia

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 3

Hi cherry11111,

Do you try the code from the reply?

Is it helpful?

BR,

Mia

If the reply helped, please "Mark Answer" and upvote it.--Mia

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 4

Hi cherry11111,

Do you try the code from the reply?

Is it helpful?

BR,

Mia

If the reply helped, please "Mark Answer" and upvote it.--Mia

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 5

Hi cherry11111,

Do you try the code from the reply?

Is it helpful?

BR,

Mia

If the reply helped, please "Mark Answer" and upvote it.--Mia

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.