Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

patindex problem Expand / Collapse
Author
Message
Posted Thursday, October 8, 2009 12:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 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

Post #799724
Posted Thursday, October 8, 2009 12:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 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
Post #941947
Posted Wednesday, June 23, 2010 12:30 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:49 AM
Points: 411, Visits: 1,403
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
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If 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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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) 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
Post #942099
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse