[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Patindex and greed ..only want first

jobs

3/21/2007 2:02:00 PM

hello. Say I have a string as follows "hello my name is bob
#smith#_#jones#_goodbye'


I only want to grab the first string #smith#.

The below does not work.

set @pattern = '%#%#%'
select @s = PATINDEX (@pattern,@Parmin)

Thanks for any help or information.
3 Answers

Alejandro Mesa

3/21/2007 2:21:00 PM

0

Try:

declare @s varchar(8000)

set @s = 'hello my name is bob#smith#_#jones#_goodbye'

select
substring(@s, PATINDEX ('%#%#%', @s) + 1, charindex('#', @s, (PATINDEX
('%#%#%', @s) + 1)) - (PATINDEX ('%#%#%', @s) + 1));


AMB


"jobs" wrote:

> hello. Say I have a string as follows "hello my name is bob
> #smith#_#jones#_goodbye'
>
>
> I only want to grab the first string #smith#.
>
> The below does not work.
>
> set @pattern = '%#%#%'
> select @s = PATINDEX (@pattern,@Parmin)
>
> Thanks for any help or information.
>
>

xyb

3/21/2007 2:30:00 PM

0

On 3?21?, ??10?02?, "jobs" <j...@webdos.com> wrote:
> hello. Say I have a string as follows "hello my name is bob
> #smith#_#jones#_goodbye'
>
> I only want to grab the first string #smith#.
>
> The below does not work.
>
> set @pattern = '%#%#%'
> select @s = PATINDEX (@pattern,@Parmin)
>
> Thanks for any help or information.

declare @Parmin varchar(4000),@patternb varchar(20),@patterne
varchar(20),@s varchar(20)
select @Parmin = 'hello my name is bob #smith#_#jones#_goodbye'

set @patternb = '%#%#%'
set @patterne = '%#_#%'
select @s = substring(@Parmin,PATINDEX (@patternb,@Parmin),PATINDEX
(@patterne,@Parmin)-PATINDEX (@patternb,@Parmin) + 1)
select @s

jobs

3/21/2007 3:49:00 PM

0

sorry...

#whatever# won't always be in the same position and won't always be
seperated by a single character.

it needs to be able to return the first token of "#smith# in all these
cases

hello my name is bob #smith#_#jones# goodbye
hello my name is bob #smith##jones# goodbye
#smith# is my name is also #jones# goodbye

and if fed

#bob# is my name is also #jones# goodbye

would return #bob#

thank you for responses.