Post reply

how to get n th index of a character in a string

  • December 17, 2008 at 4:08 am

    #125096

    for example in the string below i want text till the 4th "/"

    hello/what/ru/doing?/happy/new year

  • December 17, 2008 at 4:27 am

    #913688

    Use this code

    select left('hello/what/ru/doing?/happy/new year

    ',len('hello/what/ru/doing?/happy/new year

    ')-(len('hello/what/ru/doing?/happy/new year

    ')-charindex('/','hello/what/ru/doing?/happy/new year

    ')))

  • December 17, 2008 at 4:36 am

    #913693

    sorry .

    when i ran this query its giving me result as 'hello/'

    and expected result is hello/what/ru/doing?/

  • December 17, 2008 at 4:38 am

    #913695

    try this:

    DECLARE @string VARCHAR(100)

    SET @string = 'hello/what/ru/doing?/happy/new year'

    DECLARE @Result VARCHAR(100)

    ;WITH MyCTE

    AS

    (SELECT TOP 4 SUBSTRING(@String+'/', n,

    CHARINDEX('/', @String+'/', n) - n) as Val

    FROM tally

    WHERE n <= LEN(@String)

    AND SUBSTRING('/' + @String,

    n, 1) = '/'

    ORDER BY n )

    SELECT @Result = ISNULL(@Result,'') + ' ' + Val FROM MyCTE

    SELECT @Result

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • December 17, 2008 at 4:49 am

    #913701

    thank you.

    but its giving the below error

    Invalid object name 'tally'.

  • December 17, 2008 at 4:55 am

    #913706

    you need to create the tally table

    SELECT TOP 11000

    IDENTITY(INT,1,1) AS N

    INTO dbo.Tally

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    ALTER TABLE dbo.Tally

    ADD CONSTRAINT PK_Tally_N

    PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • December 17, 2008 at 5:03 am

    #913715

    thanks again Christopher Stobbs

    its working and result is ---- hello what ru doing?

    you have removed all the '/' but i need them and my expected result is

    -- hello/what/ru/doing?/

  • December 17, 2008 at 5:06 am

    #913719

    cool all you need to do is change the concatenation in the last select

    DECLARE @string VARCHAR(100)

    SET @string = 'hello/what/ru/doing?/happy/new year'

    DECLARE @Result VARCHAR(100)

    ;WITH MyCTE

    AS

    (SELECT TOP 4 SUBSTRING(@String+'/', n,

    CHARINDEX('/', @String+'/', n) - n) as Val

    FROM tally

    WHERE n <= LEN(@String)

    AND SUBSTRING('/' + @String,

    n, 1) = '/'

    ORDER BY n )

    SELECT @Result = ISNULL(@Result,'') + Val + '/' FROM MyCTE

    SELECT @Result

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • December 17, 2008 at 5:16 am

    #913723

    thanks christopher !!

    thats great work!!! but i need to use this query else where

    and i dont want to create that table(tally ).

    Is there any way around for doing this

  • December 17, 2008 at 5:22 am

    #913727

    Why don't you want to create the table?

    Having a tally table is very useful...

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • December 17, 2008 at 5:23 am

    #913728

    try this:

    DECLARE @string VARCHAR(100)

    SET @string = 'hello/what/ru/doing?/happy/new year'

    DECLARE @Result VARCHAR(100)

    ;WITH MyCTE

    AS

    (SELECT TOP 4 SUBSTRING(@String+'/', n,

    CHARINDEX('/', @String+'/', n) - n) as Val

    FROM

    (SELECT TOp 100 ROW_NUMBER() OVER (PARTITION BY GETDATE() ORDER BY GETDATE()) as n

    FROM Master.dbo.SysColumns) nest

    WHERE n <= LEN(@String)

    AND SUBSTRING('/' + @String,

    n, 1) = '/'

    ORDER BY n )

    SELECT @Result = ISNULL(@Result,'') + Val + '/' FROM MyCTE

    SELECT @Result

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • December 17, 2008 at 5:29 am

    #913729

    working fine. many thanks

  • January 15, 2011 at 12:04 am

    #1273877

    See this article related to same

    Play with n'th Index of a character in string

Viewing 13 posts - 1 through 12 (of 12 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy