Post reply

I need to get the specific word from the string

  • August 18, 2012 at 3:52 am

    #260783

    Hi,

    I need to get the specific word from the string.I dont know the exact word, but the position.

    E.g:

    ABC DEF GHI JKL MNO 12 PQR

    I want to find the 5th word from the above string using the space. Say, 'MNO' is my required output.

    Is there any specific function available, otherthan charindex or patindex or substring?

    Or provide me the solution using substring,charindex or patindex in a simpler way.

    Your replies will be appreciated. Thanks in advance.

  • August 18, 2012 at 5:03 am

    #1527317

    There is no inbuilt function but there are couple of ways that can be applied to your problem.

    These approaches are trade off between performance and simplicity.

    The best approach is custom CLR function as there is string manipulation or you can use Tally table functionality if your string is not very big.

    The worst approach is Loop based function.

  • August 18, 2012 at 3:50 pm

    #1527358

    you can do this easily withe DelimitedSplit8K function here on SSC:

    declare @val varchar(8000)

    set @val='ABC DEF GHI JKL MNO 12 PQR'

    select * from dbo.DelimitedSplit8K(@val,' ')

    where ItemNumber=5

    read the article here and download the code:

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    you can also do it with the user defined CHARINDEX2 function as well, but the above method is much easier.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply