locked
Extracting a word from String RRS feed

  • Question

  • Hi,

    Is there a function / way to extract a word from a particular position in the String.

    I want to extract a word which is in the 16th position. All words are separated by spaces.

    Thanks in advance!

    Tuesday, June 30, 2015 7:43 AM

Answers

  • create table test (id int, s varchar(500))
    Insert into test values(1,'aaa 1dd 2bbb 3ccc 4bb 5ddd 6STANDARD 7aa 8bb 9cc 100 11dd 12xxx 13x7x 14y 15z 16yyyy 17p 18u 19ttt')
    ,(2,'aaa 12.35 bbb ccc 11.24 ddd STANDARD aa bb cc 199 dd xxxxx yyyyy.')
    ,(3,'aaa 1dd 2bbb 3ccc 4bb 5ddd 6STANDARD 7aa 8bb 9cc 100 11dd 12xxx 13x7x 14y 15z 16yYyyy 17p 18u 19ttt')
    ,(4,'aaa 12.35 bbb ccc 11.24 ddd STANDARD aa bb cc 199 dd xxxxx yyyyy.')
     
     
    
    ;with mycte as ( 
    Select id,  0 as i, Stuff(s,1,Charindex(' ',s),'') as s From test
    Union All
    Select id, i+1 as i, Stuff(s,1,Charindex(' ',s),'') s From mycte
    m Where Charindex(' ',s)>0
    
    )
    SELECT id,   Substring(s,1,ISNULL(Nullif(Charindex(' ',s),0),999) )  
    from mycte WHERE i=15
    
    drop table test

    • Proposed as answer by Eric__Zhang Wednesday, July 1, 2015 3:14 AM
    • Marked as answer by Eric__Zhang Tuesday, July 7, 2015 1:25 AM
    Tuesday, June 30, 2015 1:05 PM

All replies

  • Try this solution: How to select the nth word in a string?

    Also check this link : Best split function


    Please don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. web: www.ronnierahman.com


    Tuesday, June 30, 2015 8:10 AM
  • If you just want only the 16th word...

    CREATE TABLE #FORUM (ABBA VARCHAR(30))
    INSERT INTO #FORUM VALUES('THE WORLD IS BEATIFUL')
    
    SELECT SUBSTRING(ABBA,16,1) FROM #FORUM  
    

    Just use substring.

    Please Mark as Answer if this post helped you

    Tuesday, June 30, 2015 8:20 AM
  • Hi DIEGOCTN,

    I only the get the 16th character. I meant a word and not a character.


    Tuesday, June 30, 2015 8:47 AM
  • DECLARE @word VARCHAR(10)='IS'
    SELECT * FROM #FORUM
    CROSS APPLY (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS pos FROM [SplitString1](ABBA)) AS Der
    WHERE token ='IS'

    CREATE  FUNCTION [dbo].[SplitString1]
         (
             @str VARCHAR(MAX)
         )
        RETURNS @ret TABLE (token VARCHAR(MAX))
         AS
         BEGIN
         
        DECLARE @x XML 
        SET @x = '<t>' + REPLACE(@str, ' ', '</t><t>') + '</t>'
        
        INSERT INTO @ret
            SELECT x.i.value('.', 'VARCHAR(MAX)') AS token
            FROM @x.nodes('//t') x(i)
        RETURN
       END


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 30, 2015 9:26 AM
    Answerer
  • Here is a sample for separating a string into words by splitting the string using space character

    http://www.kodyaz.com/articles/sql-capitalize-first-letter-sql-capitalize-string.aspx

    The sample also capitalize the first character.

    If you check the SQL Split function at referenced tutorial, you will see that it returns the order number of each splitted value. So you can add a filtering criteria on Id of splitted value in WHERE clause like

    SELECT * FROM dbo.Split(N'a b c d e', ' ') where id = 4


    SQL Server 2016 and T-SQL Tutorials


    • Edited by eralper Tuesday, June 30, 2015 11:14 AM
    Tuesday, June 30, 2015 11:13 AM
  • create table test (id int, s varchar(500))
    Insert into test values(1,'aaa 1dd 2bbb 3ccc 4bb 5ddd 6STANDARD 7aa 8bb 9cc 100 11dd 12xxx 13x7x 14y 15z 16yyyy 17p 18u 19ttt')
    ,(2,'aaa 12.35 bbb ccc 11.24 ddd STANDARD aa bb cc 199 dd xxxxx yyyyy.')
    ,(3,'aaa 1dd 2bbb 3ccc 4bb 5ddd 6STANDARD 7aa 8bb 9cc 100 11dd 12xxx 13x7x 14y 15z 16yYyyy 17p 18u 19ttt')
    ,(4,'aaa 12.35 bbb ccc 11.24 ddd STANDARD aa bb cc 199 dd xxxxx yyyyy.')
     
     
    
    ;with mycte as ( 
    Select id,  0 as i, Stuff(s,1,Charindex(' ',s),'') as s From test
    Union All
    Select id, i+1 as i, Stuff(s,1,Charindex(' ',s),'') s From mycte
    m Where Charindex(' ',s)>0
    
    )
    SELECT id,   Substring(s,1,ISNULL(Nullif(Charindex(' ',s),0),999) )  
    from mycte WHERE i=15
    
    drop table test

    • Proposed as answer by Eric__Zhang Wednesday, July 1, 2015 3:14 AM
    • Marked as answer by Eric__Zhang Tuesday, July 7, 2015 1:25 AM
    Tuesday, June 30, 2015 1:05 PM