PAtTINDEX in text - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

PAtTINDEX in text

Author

Topic

ya3mro
Starting Member

37 Posts

Posted - 2006-06-05 : 12:50:26

if i have a field with 500 Character as string and i want to select 20 Characters only in which at last index of ""
for emaxple if string is " you are in the world of computer sciecne and inforamtion systems"

if select Subtring(string ,0,15) give me "" you are in the world of com"

i want it to be returned at the end of word as
" you are in the world of computer"

plz help me

How I Came To Islam?
http://english.islamway.com

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-05 : 13:06:20

I'm not sure I follow your example, but maybe you want something like this...

--data
declare @t table (v varchar(500))
insert @t
          select ' you are in the world of computer sciecne and inforamtion systems'
union all select 'this is another sentence for the purposes of testing'
union all select 'this is a short sentence'

--input
declare @chopLength int
set @chopLength = 28

--calculation
select left(v, charindex(' ', substring(v, @chopLength, 500)) + @chopLength-2) from @t

/*results
 you are in the world of computer
this is another sentence for
this is a short sentence
*/

If not, please try to explain further - preferably with some working examples...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-05 : 13:10:13

You'll have to find
you are in the world of com
then the end of the next word
for @match = 'you are in the world of com'

select charindex(@match,fld) + len(@match)
will give the end of the string
select charindex(' ',fld,charindex(@match,fld)) + len(@match)
will give the end of the word so

select substring(fld, charindex(@match,fld), charindex(' ',fld,charindex(@match,fld)) + len(@match) - charindex(@match,fld))

shouldn't be far off.

But looking at RyanRandall's clarifying post I realise I'm nowhere near the question.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-05 : 13:20:20

LOL - I'm just guessing too, Nigel!

ya3mro - I guess you can use the above 2 posts to help you clarify what you're asking. Lots of examples is always a big help!

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-06 : 02:07:12

If you add

union all select ' you are in the world of computer'

to Rayn's example it will fail (by giving ' you are in the world of c')
this can be fixed by changing his query to

select left(v, charindex(' ', substring(v + ' ', @chopLength, 500)) + @chopLength-2) from @t

and just for the fun of it, here is what I was working on before Ryan posted his:

SELECT LEFT(v, CHARINDEX(' ', v + ' ', @chopLength) - 1) FROM @t

which was short and neat, but when tested on Ryan's data failed on the last shorter one, so I had to modify it to this:

SELECT CASE WHEN LEN(v) <= @chopLength THEN v ELSE LEFT(v, CHARINDEX(' ', v + ' ', @chopLength) - 1) END FROM @t

but now it isn't so short and neat anymore, so I would stick with Ryan's (with the small modification).

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-06 : 04:48:26

Good spot PSamsig - Thanks

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources