Charindex returning -1 - 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

General SQL Server Forums

New to SQL Server Programming

Charindex returning -1

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-05-18 : 15:24:18

Hi

Hopefully this should be a simple one for some of the sql bods out there. Got a user table with a full name column and some of the name data only has one name instead of first and last in the string. I have a sql statement to try and chop the first and last names and what happens when I encounter these single names is that charindex returns -1 an kicks up msg356 error. Here is my code if anyone can suggest a little addition to filter this out I would appreciate it:

This piece of the code is to extract the first name. I could probably do it other ways but I am learning to use charindex.

select SUBSTRING(fullname, 1, CHARINDEX(' ', fullname)-1) AS FirstName
from users

Thanks

G

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-18 : 15:27:02

Select case when charindex(' ', fullname)>0 then Substring(fullname, 1, charindex(' ', fullname)-1) else fullname end as FirstName from users

Corey

I Has Returned!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 15:27:06

select CASE WHEN fullname LIKE '% %' THEN SUBSTRING(fullname, 1, CHARINDEX(' ', fullname)-1) ELSE fullname END AS FirstName
from users

Damn, that's twice in a row, in under 2 minutes. I suck.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-18 : 15:27:54

whoops... should have been an edit to ^

quote:
Originally posted by Seventhnight

Select case when charindex(' ', fullname)>0 then Substring(fullname, 1, charindex(' ', fullname)-1) else fullname end as FirstName from users

or

Select Substring(fullname, 1, charindex(' ', fullname+' ')-1) as FirstName from users

Corey

I Has Returned!!

Corey

I Has Returned!!

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 15:33:23

he's back

What do you get with this

SELECT COUNT(*)
FROM users
WHERE LEN (REPLACE(fullname,' ','')) + 1 < LEN(fullname)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-05-18 : 15:43:49

Thanks for all reponses so far. My battery is about to go putt so will play about with it tomorrow.

G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources