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

problems using charindex function Expand / Collapse
Author
Message
Posted Friday, February 27, 2009 4:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 4, 2011 1:28 PM
Points: 30, Visits: 66
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...
Post #665606
Posted Friday, February 27, 2009 4:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,234, Visits: 13,719
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
Exploring Recursive CTEs by Example Dwain Camps
Post #665609
Posted Friday, February 27, 2009 5:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 23, 2010 8:43 AM
Points: 395, Visits: 313
Hi,

this is my first reply - wow !

set @charpos = charindex('and',@SearchTerm) instead of
set @charpos = charindex(@SearchTerm,'and')
Post #665614
Posted Friday, February 27, 2009 5:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 4, 2011 1:28 PM
Points: 30, Visits: 66
wow, i am embarrassed. i guess i need to change my user name.
Post #665617
Posted Friday, February 27, 2009 5:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 7,005, Visits: 8,453
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #665618
Posted Friday, February 27, 2009 5:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 4, 2011 1:28 PM
Points: 30, Visits: 66
allrighty, yall are just rubbing it in now
Post #665619
Posted Friday, February 27, 2009 5:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 7,005, Visits: 8,453
;)

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


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #665627
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse