patindex problem

Question

Post reply

patindex problem

kutti45

SSC Enthusiast

Points: 104
More actions
October 8, 2009 at 12:27 am

#216142

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

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

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

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*/

fcastillo 24841 Valued Member Points: 66 More actions · Answer 2

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

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 3

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

R.P.Rozema SSChampion Points: 12317 More actions · Answer 4

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?

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 5

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