CHARINDEX - 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

Author

Topic

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 13:18:42

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-02 : 13:23:59

quote:
Originally posted by Chris_Kelley

I have a column with the following values

username
------------
Roger Morse
Tina Murray
Whitney Sims
Kendra Smith

I want to split the values by first name and last name, I found this which works perfect

-SELECT LEFT('HELLO WORLD',CHARINDEX(' ','HELLO WORLD')-1)
-SELECT RIGHT('HELLO WORLD',CHARINDEX(' ','HELLO WORLD')-1)

But when I add my column name username -

SELECT LEFT(USERNAME,CHARINDEX('',USERNAME)-1)

I get

Msg 207, Level 16, State 1, Line 1
Invalid column name 'USERNAME'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'USERNAME'.

any help would be amazing, thanks everyone

Thanks,
Chris
Jr Programmer

Did you add the from clause?

SELECT LEFT(USERNAME,CHARINDEX('',USERNAME)-1) FROM YourTableNameHere

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 13:27:30

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-02 : 13:35:43

Cast it to NVARCHAR(MAX)

SELECT LEFT(CAST(USERNAME AS NVARCHAR(MAX)),CHARINDEX(' ',CAST(USERNAME AS NVARCHAR(MAX)))-1) FROM YourTableNameHere

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 13:40:53

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 13:57:45

IDK,

I am going to go to lunch and see if it comes easier after, thanks

Thanks,
Chris
Jr Programmer

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-02 : 14:07:02

It is probably because you have some records which have no space in them - e.g., a row that has only a last name? You can work around that like shown below

SELECT LEFT(CAST(USERNAME AS NVARCHAR(MAX)),CHARINDEX(' ',CAST(USERNAME AS NVARCHAR(MAX))+N' ')-1) FROM YourTableNameHere

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 15:05:25

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 15:07:59

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 15:20:37

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 15:33:49

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources