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
c

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
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 13:27:30
c
Go to Top of Page

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
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 13:40:53
c
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 15:05:25
c
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 15:07:59
c
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 15:20:37
c
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-02 : 15:33:49
c
Go to Top of Page
   

- Advertisement -