Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 patindex problem Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, October 8, 2009 12:27 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, October 8, 2009 7:32 AM Points: 2, Visits: 34
 i have table column in sql server like thisdat1/dat2/dat3/dat4/1/2009dat1/dat2/dat34/dat4/2/2009dat1/dat2/dat/sd34/dat4/3/2009ssf/sdf/sdf/sf/sf/sf/4/2009ssf/sdf/sdf/sf/sf/sf/10/2009ssf/sdf/sdf/sf/sdf/sf/sf/44444444/2009ssf/sdf/sdf/sf//sdf sdf sf/sf/111/2009ssf/sdf/sdf/sf/sf/sd sf sdf /1798/2009i want the number alone in that column like this123410444444441111798please give me the solution
Post #799724
 Posted Thursday, October 8, 2009 12:57 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, July 25, 2016 5:45 AM Points: 1,229, Visits: 3,493
 `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*/`
Post #799732
 Posted Wednesday, June 23, 2010 11:04 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, May 9, 2012 6:27 AM Points: 2, Visits: 4
 I think its a little bit late, but if helps someone .You can put some Replace in the same sentence like thisdeclare @Table As Table (col nvarchar(50))INSERT into @table(col)Select 'dat1/dat2/dat3/dat4/1/2009'UNION ALLSelect 'dat1/dat2/dat34/dat4/2/2009'UNION ALLSelect 'dat1/dat2/dat/sd34/dat4/3/2009'UNION ALLSelect 'ssf/sdf/sdf/sf/sf/sf/4/2009' UNION ALLSelect 'ssf/sdf/sdf/sf/sf/sf/10/2009'UNION ALLSelect 'ssf/sdf/sdf/sf/sdf/sf/sf/44444444/2009'UNION ALLSelect 'ssf/sdf/sdf/sf//sdf sdf sf/sf/111/2009'UNION ALLSelect '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
Post #941947
 Posted Wednesday, June 23, 2010 12:30 PM
 SSC Veteran Group: General Forum Members Last Login: Friday, December 31, 2010 9:46 AM Points: 274, Visits: 473
 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
Post #942001
 Posted Wednesday, June 23, 2010 2:58 PM
 SSC-Addicted Group: General Forum Members Last Login: Friday, November 25, 2016 6:46 AM Points: 472, Visits: 1,665
 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 ALLSELECT 'dat1/dat2/dat34/dat4/2/2009' UNION ALLSELECT 'dat1/dat2/dat/sd34/dat4/3/2009' UNION ALLSELECT 'ssf/sdf/sdf/sf/sf/sf/4/2009' UNION ALLSELECT 'ssf/sdf/sdf/sf/sf/sf/10/2009' UNION ALLSELECT 'ssf/sdf/sdf/sf/sdf/sf/sf/44444444/2009' UNION ALLSELECT 'ssf/sdf/sdf/sf//sdf sdf sf/sf/111/2009' UNION ALLSELECT 'ssf/sdf/sdf/sf/sf/sd sf sdf /1798/2009' UNION ALLSELECT '2009' union allselect 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 wordfrom cteWordswhere nr = 2;` Posting Data Etiquette - Jeff ModenPosting Performance Based Questions - Gail ShawHidden RBAR - Jeff ModenCross Tabs and Pivots - Jeff ModenCatch-all queries - Gail ShawIf you don't have time to do it right, when will you have time to do it over?
Post #942095
 Posted Wednesday, June 23, 2010 3:06 PM
 SSC Veteran Group: General Forum Members Last Login: Friday, December 31, 2010 9:46 AM Points: 274, Visits: 473
 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) ENDFROM ( 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
Post #942099

 Permissions