patindex problem

  • i have table column in sql server like this

    dat1/dat2/dat3/dat4/1/2009

    dat1/dat2/dat34/dat4/2/2009

    dat1/dat2/dat/sd34/dat4/3/2009

    ssf/sdf/sdf/sf/sf/sf/4/2009

    ssf/sdf/sdf/sf/sf/sf/10/2009

    ssf/sdf/sdf/sf/sdf/sf/sf/44444444/2009

    ssf/sdf/sdf/sf//sdf sdf sf/sf/111/2009

    ssf/sdf/sdf/sf/sf/sd sf sdf /1798/2009

    i want the number alone in that column like this

    1

    2

    3

    4

    10

    44444444

    111

    1798

    please give me the solution

  • declare @result varchar(50)

    set @result = 'ssf/sdf/sdf/sf/sf/sf/10/2009'

    select replace(@result,'/2009','')

    /*ssf/sdf/sdf/sf/sf/sf/10*/

    select reverse(replace(@result,'/2009',''))

    /*01/fs/fs/fs/fds/fds/fss*/

    select patindex('%/%',reverse(replace(@result,'/2009','')))

    /*3*/

    select left(reverse(replace(@result,'/2009','')),(patindex('%/%',reverse(replace(@result,'/2009',''))))-1)

    /*01*/

    select reverse (left(reverse(replace(@result,'/2009','')),(patindex('%/%',reverse(replace(@result,'/2009',''))))-1))

    /*10*/

  • I think its a little bit late, but if helps someone .

    You can put some Replace in the same sentence like this

    declare @Table As Table (col nvarchar(50))

    INSERT into @table(col)

    Select 'dat1/dat2/dat3/dat4/1/2009'

    UNION ALL

    Select 'dat1/dat2/dat34/dat4/2/2009'

    UNION ALL

    Select 'dat1/dat2/dat/sd34/dat4/3/2009'

    UNION ALL

    Select 'ssf/sdf/sdf/sf/sf/sf/4/2009'

    UNION ALL

    Select 'ssf/sdf/sdf/sf/sf/sf/10/2009'

    UNION ALL

    Select 'ssf/sdf/sdf/sf/sdf/sf/sf/44444444/2009'

    UNION ALL

    Select 'ssf/sdf/sdf/sf//sdf sdf sf/sf/111/2009'

    UNION ALL

    Select 'ssf/sdf/sdf/sf/sf/sd sf sdf /1798/2009'

    select LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RePLACE(REPLACE(REPLACE(REPLACE(

    col,'dat','') ,'sdf',''),'ssf',''),'2009',''),'/',''),'sf',''),'sd',''),'12344',''),'1234',''))) from @Table

  • If the string you want is always the next-to-last one and is always delimited by '/', this should do it:

    SELECT

    SUBSTRING(data, LEN(data) - CHARINDEX('/', REVERSE(data), CHARINDEX('/', REVERSE(data)) + 1) + 2,

    CHARINDEX('/', REVERSE(data), CHARINDEX('/', REVERSE(data)) + 1) - CHARINDEX('/', REVERSE(data)) - 1)

    FROM (

    SELECT 'dat1/dat2/dat3/dat4/1/2009' AS data UNION ALL

    SELECT 'dat1/dat2/dat34/dat4/2/2009' UNION ALL

    SELECT 'dat1/dat2/dat/sd34/dat4/3/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sf/sf/4/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sf/sf/10/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sdf/sf/sf/44444444/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf//sdf sdf sf/sf/111/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sf/sd sf sdf /1798/2009'

    ) AS test_data

    Scott Pletcher, SQL Server MVP 2008-2010

  • Using a common table expression you can also do neat tricks. I've interpreted your task as "return the next-to-last word from each of the /-separated list of words". In these string manipulations you have to be very careful with the substring() function: passing it a negative value for the index will result in very-hard-to-debug runtime errors. I think this should deal correctly with all sorts of input:

    declare @tbl table (

    data nvarchar(max) null

    );

    insert @tbl(data)

    SELECT 'dat1/dat2/dat3/dat4/1/2009' AS data UNION ALL

    SELECT 'dat1/dat2/dat34/dat4/2/2009' UNION ALL

    SELECT 'dat1/dat2/dat/sd34/dat4/3/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sf/sf/4/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sf/sf/10/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sdf/sf/sf/44444444/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf//sdf sdf sf/sf/111/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sf/sd sf sdf /1798/2009' UNION ALL

    SELECT '2009' union all

    select null;

    with cteWords as (

    select

    1 as nr,

    reverse(case when charindex('/', t.data) > 0

    then left(t.data, charindex('/', t.data) - 1)

    else t.data

    end) as word,

    case when charindex('/', t.data) > 0

    then RIGHT(t.data, len(t.data) - charindex('/', t.data))

    else CONVERT(nvarchar(max),null)

    end as remainder

    from @tbl tbl

    cross apply (

    select REVERSE(tbl.data) as data

    ) t

    union all

    select

    c.nr + 1 as nr,

    reverse(case when charindex('/', c.remainder) > 0

    then left(c.remainder, charindex('/', c.remainder) - 1)

    else c.remainder

    end) as word,

    case when charindex('/', c.remainder) > 0

    then right(c.remainder, len(c.remainder) - charindex('/', c.remainder))

    else null

    end as remainder

    from cteWords c

    where not c.remainder is null

    )

    select word

    from cteWords

    where nr = 2;



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • You do need at least two slashes in all data for my previous code to work. If you may not have 2 slashes, you can do this:

    SELECT

    CASE WHEN data NOT LIKE '%/%/%' THEN '' ELSE

    SUBSTRING(data, LEN(data) - CHARINDEX('/', REVERSE(data), CHARINDEX('/', REVERSE(data)) + 1) + 2,

    CHARINDEX('/', REVERSE(data), CHARINDEX('/', REVERSE(data)) + 1) - CHARINDEX('/', REVERSE(data)) - 1) END

    FROM (

    SELECT 'dat1/dat2/dat3/dat4/1/2009' AS data UNION ALL

    SELECT 'dat1/dat2/dat34/dat4/2/2009' UNION ALL

    SELECT 'dat1/dat2/dat/sd34/dat4/3/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sf/sf/4/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sf/sf/10/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sdf/sf/sf/44444444/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf//sdf sdf sf/sf/111/2009' UNION ALL

    SELECT 'ssf/sdf/sdf/sf/sf/sd sf sdf /1798/2009' UNION ALL

    SELECT '' UNION ALL

    SELECT '2009'

    ) AS test_data

    Scott Pletcher, SQL Server MVP 2008-2010

Viewing 6 posts - 1 through 5 (of 5 total)

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