locked
Fetch element at particular position RRS feed

  • 問題

  • 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.


    2020年8月11日 下午 08:24

所有回覆

  • 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, esquel@sommarskog.se

    2020年8月11日 下午 09:25
  • 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.

    2020年8月12日 上午 07:09
  • 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.

    2020年8月13日 上午 01:22
  • 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.

    2020年8月14日 上午 01:05
  • 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.

    2020年8月17日 上午 01:11