problems using charindex function

Question

Post reply

problems using charindex function

brd123

SSC Eights!

Points: 874
More actions
February 27, 2009 at 4:47 am

#131413

Ummm, ok, stupid question or stupid person ?? Why is the following not working? Or why is the position of the 'and' string not being found?
declare @charpos int
declare @searchterm nvarchar(max)
SET @searchterm = 'blood and sweat'
set @charpos = charindex(@SearchTerm,'and')
IF (@charpos = 0) --Check to make sure string position was found
BEGIN
print 'charindex not working as I think it should 🙂 '
print cast(@charpos as varchar(max))
END
-----------------------------
many thanks...:crazy:

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Have some coffee - the expressions are the wrong way round in the function 🙂

declare @searchterm nvarchar(200)

SET @searchterm = 'blood and sweat'

SELECT charindex(@SearchTerm,'and'), charindex('and', @SearchTerm)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

321 MySQL Right there with Babe Points: 750 More actions · Answer 2

Hi,

this is my first reply - wow !

set @charpos = charindex('and',@SearchTerm) instead of

set @charpos = charindex(@SearchTerm,'and')

brd123 SSC Eights! Points: 874 More actions · Answer 3

wow, i am embarrassed. i guess i need to change my user name. :ermm:

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 4

declare @charpos int

declare @searchterm nvarchar(max)

SET @searchterm = 'blood and sweat'

set @charpos = charindex('and', @SearchTerm)

IF (@charpos = 0) --Check to make sure string position was found

BEGIN

print 'charindex not working as I think it should [Smile] '

print cast(@charpos as varchar(max))

END

else

BEGIN

print 'charindex is working [LOL]'

print cast(@charpos as varchar(max))

END

highlight "charindex" in your query panel and press F1

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

brd123 SSC Eights! Points: 874 More actions · Answer 5

brd123

SSC Eights!

Points: 874

February 27, 2009 at 5:17 am

#951071

allrighty, yall are just rubbing it in now

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 6

😉

What always tends to bring the confusing with charindex/patindex is that is has its arguments in another order then the replace function.

Whenever I need to use anyone of those, F1 is being used :w00t: