charindex incorrect

[morechocolate]

using the statement charindex(PHIST.LOAN, p.loan_name)}

and given

phist.loan = 111111
p.loan_name = property name 111111

would return 15

but

phist.loan = 222222
p.loan_name = property name 222222 (other text)

would return 0 even though the loan is within the loan_name

I cannot figure this out to save my life

 

[SQLDenis]

works here

declare @LOAN varchar(30)
declare @LOANname varchar(130)
select @LOAN = '111111'
select @LOANname ='property name 111111'

select charindex(@LOAN, @LOANname)

GO

declare @LOAN varchar(30)
declare @LOANname varchar(130)
select @LOAN = '222222'
select @LOANname ='property name 222222 (other text)'

select charindex(@LOAN, @LOANname)
GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[morechocolate]

SQLDenis when I put in what you provided above it works,

but when I do the following

select charindex(p.loan,rtrim(substring(p.loan_name,1,charindex('(',p.loan_name)-1)))
from lmast p
where p.loan = '3333333'

and p.loan_name = PROP NAME 3333333 (NOTE F)

it returns 0

same thing if I do

select charindex(p.loan,,p.loan_name)
from lmast p
where p.loan = '3333333'

but, if I replace p.loan with '3333333' I get the correct results

see - weird

 

[SQLDenis]

do you get the same values here, I suspect not

select len(p.loan) ,len('3333333')
from lmast p
where p.loan like '%3333333%'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[morechocolate]

I know where you were going and was hoping they would have been different, but they were the same results.

 

[SQLDenis]

Even this works for me, same code you have
declare @LOAN varchar(30)
declare @LOANname varchar(130)
select @LOAN = '222222'
select @LOANname ='property name 222222 (other text)'

select charindex(@LOAN,rtrim(substring(@LOANname,1,charindex('(',@LOANname)-1)))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[morechocolate]

yeah that worked for me too...I give up

 

[SQLDenis]

copy and paste the values in editplus or textpad and make sure that there aren't any hidden characters like linefeeds or carriage returns anywhere

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[morechocolate]

Lord I hate stupid issues. SQLDenis I finally figured it out. BTW - I put in editplus and did not see any hidden characters, but I decided to try something. Eventhough the len test showed the length to be equal I said why not just do a rtrim on p.loan. Guess what? That worked. UGH!!! I spent hours just to come to that!!!

Thanks for all your help.